sp_executesql返回多个输出参数

参考下面sp_executesql单个输出参数，@I变量为动态列名,列名后缀n或是c，为了比较更新前后值是否发生了变化，分别做了两次SELECT取值，程代码冗余和产生不容忽视性能的问题。：

View Code                 SET @sql = N'SELECT @N = ['+ CONVERT(NVARCHAR(MAX),@I) +'n] FROM #inserted'
                EXECUTE sp_executesql @sql,
                                      N'@N DECIMAL(18,0) OUTPUT',
                                      @Nn OUTPUT;
                SET @sql = N'SELECT @O = ['+ CONVERT(NVARCHAR(MAX),@I) +'n] FROM #deleted'        
                EXECUTE sp_executesql @sql,
                                      N'@O DECIMAL(18,0) OUTPUT',
                                      @On OUTPUT;    
                IF (ISNULL(@Nn,0) <> ISNULL(@On,0))                
                    --EXECUTE [dbo].[usp_Audit_Insert] ...                           
            
                
                SET @sql = N'SELECT @N = ['+ CONVERT(NVARCHAR(MAX),@I) +'c] FROM #inserted'
                EXECUTE sp_executesql @sql,
                                      N'@N DECIMAL(18,0) OUTPUT',
                                      @Nc OUTPUT;
                SET @sql = N'SELECT @O = ['+ CONVERT(NVARCHAR(MAX),@I) +'c] FROM #deleted'        
                EXECUTE sp_executesql @sql,
                                      N'@O DECIMAL(18,0) OUTPUT',
                                      @Oc OUTPUT;    
                IF (ISNULL(@Nc,0) <> ISNULL(@Oc,0))                
                    --EXECUTE [dbo].[usp_Audit_Insert] ...

 

sp_executesql可以实现返回多个输出参数，改进代码：

View Code DECLARE @sql NVARCHAR(MAX),@FName NVARCHAR(50)
                DECLARE @Nn DECIMAL(18,0),@On DECIMAL(18,0),@Nc DECIMAL(18,0),@Oc DECIMAL(18,0)    
                                
                SET @sql = N'SELECT @Nn = ['+ CONVERT(NVARCHAR(MAX),@I) +'n],@Nc = ['+ CONVERT(NVARCHAR(MAX),@I) +'c] FROM #inserted'
                EXECUTE sp_executesql @sql,
                                      N'@Nn DECIMAL(18,0) OUTPUT,@Nc DECIMAL(18,0) OUTPUT',
                                      @Nn OUTPUT,@Nc OUTPUT;                                      
                                      
                SET @sql = N'SELECT @On = ['+ CONVERT(NVARCHAR(MAX),@I) +'n],@Oc = ['+ CONVERT(NVARCHAR(MAX),@I) +'c] FROM #deleted'        
                EXECUTE sp_executesql @sql,
                                      N'@On DECIMAL(18,0) OUTPUT,@Oc DECIMAL(18,0) OUTPUT',
                                      @On OUTPUT,@Oc OUTPUT;
                                          
                IF (ISNULL(@Nn,0) <> ISNULL(@On,0))
                    --EXECUTE [dbo].[usp_Audit_Insert] ...
                                
                IF (ISNULL(@Nc,0) <> ISNULL(@Oc,0))
                    --EXECUTE [dbo].[usp_Audit_Insert] ...

 

 更多相关：

http://www.cnblogs.com/insus/archive/2012/01/18/2325299.html