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

Answer 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 

Answer 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