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

来源：互联网发布：java防止重复提交编辑：程序博客网时间：2024/04/30 14:07

存储过程部分

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 岗位工资和

0 0