Menu
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
390
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
Or you can assign a single row-single column SELECT statement's result to a variable by the SET keyword: SET @YourVariable = (SELECT COUNT(1) FROM YourTable). You can not mix the above options. Furthermore, CTE is defined within the execution scope of a single SELECT , INSERT , UPDATE , or DELETE statement.
Temp Variables are created using a “DECLARE” statement and are assigned values using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a specific batch of query execution.
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 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
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
