sql-server - SQL Server 2008 存储过程不从动态查询返回输出

Question

以下存储过程动态确定指定表中的列并返回所有不为空的列，但是存储过程返回空，尽管在自行测试动态查询时返回结果，我是否正在处理动态查询正确返回结果？

CREATE PROCEDURE [dbo].[spGetTables] 
(
    @TableName    varchar(255)
,@ParamOut    varchar(2000) OUTPUT
)
AS

declare @CommaString varchar(max)
set @CommaString = ''

Declare @col varchar(255), @cmd nvarchar(max)
declare @colName varchar(2000)
SET @colName = ''

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName 

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NULL or len([' + @col + ']) < 1) BEGIN select ''' + @col + ',''  END'

    exec sp_executesql @cmd, N'@Result varchar(max) out', @ParamOut out

    set @ParamOut = @ParamOut + @CommaString

    FETCH NEXT FROM getinfo into @col
END

PRINT 'Result: '+@ParamOut

CLOSE getinfo
DEALLOCATE getinfo
RETURN 0

Answer 1

将过程更改为此过程并重试:

CREATE PROCEDURE [dbo].[spGetTables] 
(
    @TableName    varchar(255)
,@ParamOut    varchar(2000) OUTPUT
)
AS

declare @CommaString varchar(max)
set @CommaString = ''

Declare @col varchar(255), @cmd nvarchar(max)
declare @colName varchar(2000)
SET @colName = ''

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = @TableName 

OPEN getinfo

FETCH NEXT FROM getinfo into @col

DECLARE @TempParamOut varchar(2000) = '';

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @cmd = ' IF NOT EXISTS (SELECT top 1 * FROM [' + @TableName + '] WHERE [' + @col + '] IS NULL or len([' + @col + ']) < 1) BEGIN select @Result= ''' + @col + ',''  END'

    exec sp_executesql @cmd, N'@Result varchar(max) out', @TempParamOut out

    set @ParamOut = ISNULL(@ParamOut,'') + ISNULL(@TempParamOut,'') + @CommaString;
    set @TempParamOut = '';
    FETCH NEXT FROM getinfo into @col
END


CLOSE getinfo
DEALLOCATE getinfo
RETURN 0

在动态T-SQL 语句中，您需要将SELECT 的值赋给变量。此外，有时此输出值可以是 NULL 但 STRING + NULL 等于 NULL。我添加了一个缓冲区变量 @TempParamOut 用于存储中间结果。您正在使用 @ParamOut 变量并在每次执行动态 T-SQL 语句时覆盖它的值。这就是根本不执行连接的原因。