Как установить значение переменной, используя 'execute' в t-sql?

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

Когда я запускаю script выше, я получаю следующую ошибку

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

(1 row(s) affected)

Почему и как его исправить?

Спасибо

Ответ 1

Вы можете использовать выходные параметры с помощью 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

Ответ 2

Динамический SQL - это другая область внешней, вызывающая SQL: поэтому @siteid не распознается

Вам придется использовать переменную temp table/table за пределами динамического SQL:

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

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

select * FROM @siteId

Примечание: TOP без ORDER BY не имеет смысла. Для таблицы нет естественного, подразумеваемого или внутреннего порядка. Любой заказ гарантируется только внешним ORDER BY

Ответ 3

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