如何将查询结果存储在变量中

SQL Server stored procedure:

SQL Server存储过程：

CREATE PROCEDURE [dbo].[CheckTableStatus] 
    @DatabaseName AS NVARCHAR(50) = 'DBA',
    @ProjectID AS NVARCHAR(50) = 'CommandLog'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableCount as int

    SET @Temp = 'DECLARE @cnt as int;'
    SET @Temp = @Temp + 'USE '+ @DatabaseName +'; SELECT @cnt=COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME=''' + @ProjectID + ''';'

    PRINT @Temp

    EXEC sp_executesql @temp

    --ASSIGN OUTPUT TO @TableCount
    IF @TableCount > 0  
       -- Do something
END

How do I assign the results of @temp being executed to variable @TableCount?

如何将正在执行的@temp的结果分配给变量@TableCount？

3 个解决方案

#1

Use the OUTPUT parameter:

使用OUTPUT参数：

CREATE PROCEDURE [dbo].[CheckTableStatus] 
    @DatabaseName as nvarchar(50) = 'DBA',
    @ProjectID as nvarchar(50) = 'CommandLog'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TableCount as int,@Temp nvarchar(max)='';
--    SET @Temp = 'DECLARE @cnt as int;'
    SET @Temp = @Temp + 'USE '+ @DatabaseName +'; SELECT @cnt=COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME=''' + 
@ProjectID + ''';'
    print @Temp
    EXEC sp_executesql @temp,
      N'@cnt int out',@TableCount out;
    --ASSIGN OUTPUT TO @TableCount
    IF @TableCount >0 
     -- Do something
        print @TableCount;
END

But why not use the OBJECT_ID function?

但为什么不使用OBJECT_ID函数？

Also your code is prone to SQL-injection.

您的代码也很容易注入SQL。

#2

If your goal is to get the count from @temp, try using an OUTPUT with your dynamic SQL:

如果您的目标是从@temp获取计数，请尝试使用动态SQL的OUTPUT：

EXEC sp_executesql @temp, N'@cnt int OUTPUT', @TableCount OUTPUT

These additions to sp_executesql will output the @cnt value in your dynamic SQL to the @TableCount in your current context.

sp_executesql的这些新增功能会将动态SQL中的@cnt值输出到当前上下文中的@TableCount。

#3

You can set value as

您可以将值设置为

SET @TableCount = @cnt

#1