如何正确使用输出参数作为变量

admin

我有这个代码

DECLARE @columnName varchar(25)
DECLARE @cursor CURSOR
DECLARE @sql nvarchar(1000)
DECLARE @tableName as nvarchar(30)
DECLARE @PartNo as nvarchar(30)
DECLARE @value as integer

set @PartNo = 'partnumber'
set @tableName = 'sometable'

SET @cursor = CURSOR FOR 
                  SELECT c.name 
                  FROM sys.columns c 
                  INNER JOIN sys.tables t ON c.object_id = t.object_id 
                  WHERE c.object_id = (SELECT object_id FROM sys.tables 
                                       WHERE name  = @tableName)
                    AND c.name NOT IN ('something')

OPEN @cursor

FETCH NEXT FROM @cursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'select sum('[email protected]+') from checktime where partNo = '''[email protected]+''''

    EXEC sp_executesql @sql, N'@columnName varchar(25), @PartNo nvarchar(30), @value int OUTPUT',
                   @columnName,
                   @PartNo,
                   @value OUTPUT

    FETCH NEXT FROM @cursor INTO @columnName
END

CLOSE @cursor
DEALLOCATE @cursor

这段代码的问题是，即使我不想输出变量，它也已经输出了。我想使用这样的代码

 DECLARE @columnName varchar(25)
    DECLARE @cursor CURSOR
    DECLARE @sql nvarchar(1000)
    DECLARE @tableName as nvarchar(30)
    DECLARE @PartNo as nvarchar(30)
    DECLARE @value as integer

    set @PartNo = 'partnumber'
    set @tableName = 'sometable'
    SET @cursor = CURSOR FOR 
    select c.name from sys.columns c inner join sys.tables t on c.object_id = t.object_id 
    where c.object_id = (select object_id from sys.tables where name  = @tableName )
    and c.name not in ('something')

    OPEN @cursor

    FETCH NEXT

    FROM @cursor INTO @columnName
    WHILE @@FETCH_STATUS = 0
    BEGIN


    set @sql = 'select sum('[email protected]+') from checktime where partNo = '''[email protected]+''''

    EXEC sp_executesql @sql, N'@columnName varchar(25), @PartNo nvarchar(30), @value int OUTPUT',
                       @columnName,
                       @PartNo,
                       @value OUTPUT

select case when @value > 0 then 'YEY' end 

    FETCH NEXT
    FROM @cursor INTO @columnName
    END

    CLOSE @cursor
    DEALLOCATE @cursor

我正在创建一个存储过程，它将搜索列名是否为NULL，如果是，我会将其放入select语句中。我正在构建一个动态SQL，其中输出将只生成所有非空的表，但这个任务，我将自己创建它，我只想知道我如何操作输出变量。

转载请注明出处:http://www.fymidi.com/article/20230526/1355188.html