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
This question is related to
sql-server
tsql
You can use output parameters with sp_executesql.
DECLARE @dbName nvarchar(128) = 'myDb'
DECLARE @siteId int
DECLARE @SQL nvarchar(max) = N'SELECT TOP 1 @siteId = Id FROM ' + quotename(@dbName) + N'..myTbl'
exec sp_executesql @SQL, N'@siteId int out', @siteId out
select @siteId
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
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
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
Source: Stackoverflow.com