存储过程高手求解
select sum(Convert(int,t.para_value)) ,t.para_key from (
select * from biz_order_para where order_id in (2906,2907,2908,2909,2910,2911)
) t where t.para_key in ('Quota','Traffic','DBSpace') group by t.para_key
怎么把这个写成一个存储过程，说明：in 后面的需要从程序里面动态传入
[解决办法]
create Procedure sp_Test
@Id varchar(1000),
@Key varchar(2000)
as
begin
select @Id=''''+REPLACE(@Id,',',''',''')+''''
select @Key=''''+REPLACE(@Id,',',''',''')+''''
exec('sql语句')
end
你自己把那个sql语句拼接吧 上面定义的两个变量替代in里面的
[解决办法]
ALTER PROC up_test
@orderid NVARCHAR(max),
@parakey NVARCHAR(max)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(MAX)
SET @sql = N'
SELECT SUM(CONVERT(INT, t.para_value)) ,
t.para_key
FROM ( SELECT *
FROM biz_order_para
WHERE order_id IN ({1})
) t
WHERE t.para_key IN ({2})
GROUP BY t.para_key
'
SET @sql = REPLACE(@sql, '{1}', @orderid)
SET @sql = REPLACE(@sql, '{2}', @parakey)
--print @sql
EXEC(@sql)
GO
EXEC up_test ' 2906, 2907, 2908, 2909, 2910, 2911', '''Quota'', ''Traffic'', ''DBSpace'''
