sql server-通过执行@ Variable2在存储的proc中设置@ Variable1

(sql server - Set @Variable1 within a stored proc by executing @Variable2)

发布于 2020-11-25 21:57:22

我想通过在存储过程的Begin Try中执行@Counter来设置@Count的值。

SET @Counter ='SET @Count = (SELECT COUNT(' + @COLUMN + ') FROM ' + @TABLE + ' WHERE CONVERT(VARCHAR(MAX),' + @COLUMN + ') = ''' + @DATATOFIND + ''')'

我已经测试了上面的代码，它确实为我提供了在存储过程之外的普通sql语句中填充@Count变量的预期结果。

填充@Count变量后，我想在打印语句中使用它。

PRINT '-- No. of Entries in the ' + @TABLE + ' Table = ' + @Count

我尝试了以下两个选项来填充@Count，但都没有用

EXEC @Counter

and

EXECUTE sp_executesql (@Counter)

更新：

经过更多研究后，我尝试了以下方法：

DECLARE @Counter NVARCHAR(1000)
SET @Counter = N'DECLARE @Count NVARCHAR(100); SET @COUNT = (SELECT COUNT(UserId) FROM UserGrp WHERE CONVERT(VARCHAR(MAX),UserId) = ''za02'')'
EXECUTE sp_executesql @Counter
Print @Count

但我收到此错误：必须声明标量变量“ @Count”

更新：解决方法/我的情况的解决方案

DECLARE @Counter NVARCHAR(2000)
SET @Counter = 'DECLARE @Count NVARCHAR(100); SET @COUNT = (SELECT COUNT(UserId) FROM UserGrp WHERE CONVERT(VARCHAR(MAX),UserId) = 'to01'); Print '/*  No. of Entries in the UserGrp Table - ' + @Count + ' */''
EXEC (@Counter)

这为我提供了清晰的信息，以便从其余过程中决定如何处理创建的代码

Questioner

zucchini

Viewed

Original

English

DECLARE @Counter NVARCHAR(1000); DECLARE @COUNT BIGINT; DECLARE @DATATOFIND VARCHAR(100) = 'za02'; DECLARE @TABLE SYSNAME = N'UserGrp'; DECLARE @COLUMN SYSNAME = N'UserId'; SET @Counter = N'SELECT @COUNT = COUNT(<column_name>) FROM <table_name> WHERE CONVERT(VARCHAR(MAX),<column_name>) = @DATATOFIND;'; SET @Counter = REPLACE(@Counter, '<column_name>', QUOTENAME(@COLUMN)); SET @Counter = REPLACE(@Counter, '<table_name>', QUOTENAME(@TABLE)); PRINT @Counter; -- debug EXECUTE sp_executesql @Counter, N'@DATATOFIND VARCHAR(100), @COUNT BIGINT OUTPUT', @DATATOFIND, @COUNT OUTPUT; SELECT @COUNT;

sql server-通过执行@ Variable2在存储的proc中设置@ Variable1

(sql server - Set @Variable1 within a stored proc by executing @Variable2)

热门帖子

热门github