How to set value to variable using 'execute' in t-sql?

Question

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int 
exec ('SELECT TOP 1 @siteId = Id FROM ' + @dbName + '..myTbl')  
select @siteId

When I run the script above I get the following error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@siteId".

(1 row(s) affected)

Why and how to fix it?

Thank you

Solution 1

You can use output parameters with sp_executesql.

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int 
DECLARE @SQL nvarchar(max) = N'SELECT TOP 1 @outputFromExec = Id FROM ' + quotename(@dbName) + N'..myTbl'
exec sp_executesql @SQL, N'@outputFromExec int out', @siteId out
select @siteId

Solution 2

The dynamic SQL is a different scope to the outer, calling SQL: so @siteid is not recognised

You'll have to use a temp table/table variable outside of the dynamic SQL:

DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId TABLE (siteid int)

INSERT @siteId
exec ('SELECT TOP 1 Id FROM ' + @dbName + '..myTbl')  

select * FROM @siteId

Note: TOP without an ORDER BY is meaningless. There is no natural, implied or intrinsic ordering to a table. Any order is only guaranteed by the outermost ORDER BY

Solution 3

You can try like below

DECLARE @sqlCommand NVARCHAR(4000)
DECLARE @ID INT
DECLARE @Name NVARCHAR(100)
SET @ID = 4
SET @sqlCommand = 'SELECT @Name = [Name]
FROM [AdventureWorks2014].[HumanResources].[Department]
WHERE DepartmentID = @ID'
EXEC sp_executesql @sqlCommand, N'@ID INT, @Name NVARCHAR(100) OUTPUT',
@ID = @ID, @Name = @Name OUTPUT
SELECT @Name ReturnedName

Source : blog.sqlauthority.com

Comments

[ZygD]

+1 This is the nicer way. I saw a parameterised database name and dismissed sp_executesql...

[RyanfaeScotland]

"TOP without an ORDER BY is meaningless" - Meaningless is probably a bit strong here, maybe all I want is 1 record and I don't care which, i.e. what restaurant should I eat at tonight?

[ZygD]

@RyanfaeScotland: maybe, but then there is no ANY() fucntion in SQL that gives arbitray rows. Otherwise I'd want my query to behave the same way every time instead of relying on some assumed order.

[Matt]

Your note just simply isn't true in most practical scenarios. A maintained, clustered index on the table will ensure a consistent, predictable order to the table results.

[ZygD]

@Matt: do you maintain the clustered index after every update or insert that would result in fragmentation?

[Matt]

Time-dependent variables or proxies ensure that your ordering get implicitly maintained chronologically. Your point can be a valid one, but the larger point of saying there is no "natural, implied or intrinsic ordering" is just not true, which was my point. There is predictability in the physical storage location of a record, which translates to a time-dependent predictability in the TOP ordering. Of course, this does not presuppose it's a useful order for a given query's purpose, but it is predictable.

[Aamol]

Cool, I found what I was looking for !

[Paulo Henrique]

I am not able to use @siteId afterwards... it returns NULL.The execute line works fine...

[Denziloe]

@PauloHenrique Don't forget the final OUT in the EXEC line or it will fail silently.