存储过程返回参数的方法(也是执行动态sql 实现列名成为变量的功能)

2/13/2017来源:SQL技巧人气:1284

存储过程部分

USE [PaymentAnalysis]

GO /****** Object:  StoredPRocedure [dbo].[Test]    Script Date: 2017/2/10 14:18:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[Test] ( @Year nvarchar(10), @option nvarchar(Max), @All Decimal(18,2) output ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;     Declare @sql nvarchar(MAX) set @sql = 'select @sum =  sum(Convert(Decimal(18,2),'+@option+'))  from  dbo.StaticPM'+@Year exec sp_executesql @sql,N'@sum Decimal(18,2) out',@All out select @All as 函数内岗位工资和

END

执行部分

--select sum(s.护理10占比) from ( Declare @All Decimal(18,2) exec dbo.Test '2015','岗位工资',@All output select @All as 岗位工资和