创建动态存储过程

USE [SapAllData]
GO

CREATE proc [dbo].[p_cashflow_c]
(
@dbname varchar(50) ,
@stime varchar(10) ,
@etime varchar(10)
)

as
declare @stimeb varchar(10),
@stimec varchar(10)
set @stimeb=(select dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,@stime),0)))
set @stimec=(select convert(nvarchar(20),year(@stime)-1)+’-12-31’)

begin
SET NOCOUNT ON
DECLARE @sql nvarchar(max)
set @sql=
’
select
sum(case when t1.refdate <=’’’+@stimeb+’’‘then t0.Debit-t0.Credit else 0 end ) as zongjis,
sum(case when t1.refdate <=’’’+@stimec+’’‘then t0.Debit-t0.Credit else 0 end ) as zongjie
from ‘+’ ’ +@dbname+’…JDT1 t0
inner join ‘+’ ’ +@dbname+’…OJDT t1 on t1.TransId=t0.TransId
where t0.Account like ‘‘1001%’’ or t0.Account like ‘‘1002%’’ or t0.Account like ‘‘1003%’’

’
exec sp_executesql @sql

end
GO