当存储过程以动态 sql 编写时，无法从存储过程中获得输出变量

0 人关注

我正在编写一个程序来产生一个int输出变量，但我不知道如何使用动态sql来完成这个任务。如果我执行下面的过程，我可以在结果屏幕上显示@AnlyNum的值，但我只想让@AnlyNum变量设置一个值，以便我可以使用它。谢谢你。

Create procedure [dbo].[sp_test] @Db varchar(50), @RwNum int, @AnlyNum int output
Begin
Declare @Sql nvarchar(max) =
'Select ''@AnlyNum'' = (Select AnlyId From '+@Db+'..Test order by AnlyId desc OFFSET '+convert(varchar(10),@RwNum)+' rows fetch next 1 rows only)'
exec(@Sql)


            
             
              一般建议不要使用
              
               
                EXEC (@SQL);
               
              
              这样的语法。这样的语句不能被参数化，这助长了导致SQL注入等安全缺陷的坏习惯。如果你需要运行一个在变量或字面字符串中的语句，那么使用
              
               
                
                 sys.sp_executesql
                
               
              
              .然后，如果需要的话，你可以很容易地对语句进行参数化处理（在这种情况下，可以很容易地消耗动态语句中的
              
               
                OUTPUT
               
              
              参数）。


            
             
              另外，你的代码
              
               很
              
              
               危险
              
              。它很容易受到SQL注入攻击（正如我在前面的评论中提到的使用
              
               
                EXEC (@SQL)
               
              
              语法时）。
              
               永远不要
              
              把未消毒的值注入到动态语句中。正确地用
              
               
                QUOTENAME
               
              
              引用动态对象，正确地设置参数。


            
             
              另外，作为参考，前缀
              
               
                sp_
               
              
              是由微软保留的，用于
              
               特殊
              
              /
              
               系统
              
              
               程序
              
              。它
              
               不
              
              应该被用于用户程序。这样做会带来性能上的损失，而且在更新/升级后，你的程序有可能根本无法工作。要么使用一个不同的前缀，要么（可能更好）不使用前缀。
              
               sp_前缀仍然是一个禁区吗？


            
             
              谢谢你，非常感激。

sql


         
          sql-server


         
          tsql


         
          stored-procedures


         
          dynamic-sql


          
           
            
            
             Aaron Bertrand
            
           
           
            发布于
            
            2022-03-10


          
           已采纳


           
            
             这消除了SQL注入的顾虑，因为它正确地转义了数据库的名称，并且动态地针对该数据库执行，而不是在命令中嵌入数据库的名称。另外，你不需要
             
              
               @RwNum
              
             
             是动态的。
            
            CREATE PROCEDURE dbo.test
  @Db      sysname,
  @RwNum int,
  @AnlyNum int output
BEGIN
SET NOCOUNT ON;
  DECLARE @exec nvarchar(max) = QUOTENAME(@Db) + N'.sys.sp_executesql',
          @sql nvarchar(max) = N'SELECT @AnlyNum = AnlyId 
            From dbo.Test order by AnlyId desc 
            OFFSET @RwNum rows fetch next 1 rows only);';