存储过程高手求解

[西南黑少]

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 后面的需要从程序里面动态传入

[csl_1022]

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里面的

[Shawn]

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'''