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
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
OUT in the EXEC line or it will fail silently.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
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.
TOP ordering. Of course, this does not presuppose it's a useful order for a given query's purpose, but it is predictable.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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Andrew Foster
-- Create date: 28 Mar 2013
-- Description: Allows the dynamic pull of any column value up to 255 chars from regUsers table
-- =============================================
ALTER PROCEDURE dbo.PullTableColumn
(
@columnName varchar(255),
@id int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @columnVal TABLE (columnVal nvarchar(255));
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT ' + @columnName + ' FROM regUsers WHERE id=' + CAST(@id AS varchar(10));
INSERT @columnVal EXEC sp_executesql @sql;
SELECT * FROM @columnVal;
END
GO
A slight change to Mikael's version if you want to use hardcoded nvarchar value without involving the table
DECLARE @value nvarchar(200)
DECLARE @SQL nvarchar(max) = N'SELECT @outputFromExec=''lorem ipsum'''
exec sp_executesql @SQL, N'@outputFromExec nvarchar(200) out', @value out
--Tests
Select @value
Exec ('Select ''' + @value+ '''')
--Results
lorem ipsum
(1 row affected)
-----------
lorem ipsum
(1 row affected)
A slight change in the execute query will solve the problem:
DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int
exec ('SELECT TOP 1 **''@siteId''** = Id FROM ' + @dbName + '..myTbl')
select @siteId
