请问一条存储过程

2013-07-01

请教一条存储过程create proc SJZ_WMS_JCASbegindeclare @a DATETIME,set @adateadd(day,0,datediff(day,

请教一条存储过程

create proc SJZ_WMS_JC
AS
begin

declare @a DATETIME,
    set @a=dateadd(day,0,datediff(day,0,getdate()))

select 开始时间=dateadd(week,-1,@a),结束时间=dateadd(ss,-1,@a)


/*领货记录*/
SELECT 站点=dbo.uf_GetStationName(station_id),
       领货总数=COUNT(trans_id)
       FROM yw_transinfor 
WHERE input_date between dateadd(week,-1,@a) and dateadd(ss,-1,@a)
AND station_id IN('XZ82','XZ85','XZ36','XZ55','XZ61','XZ62','XZ66','JP01','XZ67','XZ68','XZ74','XZ81','XZ83','XZ84','XZ93'
)
GROUP BY station_id
ORDER BY station_id ASC

In里面我想声明为变量。然后查询的时候 EXEC My_CC '张三'就是查询张三的In条件，
陈四
刘伟
……('XZ16','XZ17','XZ18','XZ19','XZ20','XZ24','XZ27','XZ28','XZ29','XZ46','XZ50','XZ51','XZ54')
陈四
'XZ56','XZ58','XZ59','XZ80','XZ87','XZ88','XZ89','XZ91','XZCE','XZCI','XZ32','XZCM','XZCO','XZCQ','XZCD'
刘伟
'XZ82','XZ85','XZ36','XZ55','XZ61','XZ62','XZ66','JP01','XZ67','XZ68','XZ74','XZ81','XZ83','XZ84','XZ93'

[解决办法]

create proc SJZ_WMS_JC

@name varchar(20) --参数：传递姓名
AS
begin
 
declare @a DATETIME
    set @a=dateadd(day,0,datediff(day,0,getdate()))
 
select 开始时间=dateadd(week,-1,@a),结束时间=dateadd(ss,-1,@a)
 
 
/*领货记录*/
SELECT 站点=dbo.uf_GetStationName(station_id),
       领货总数=COUNT(trans_id)
       FROM yw_transinfor 
WHERE input_date between dateadd(week,-1,@a) and dateadd(ss,-1,@a)
AND station_id IN('XZ82','XZ85','XZ36','XZ55','XZ61','XZ62','XZ66',
'JP01','XZ67','XZ68','XZ74','XZ81','XZ83','XZ84','XZ93') 
and name=case when ISNULL(@name,'')='' then name else @name end  --这里加上传递的参数条件
GROUP BY station_id
ORDER BY station_id ASC

end

热点排行

SQL Server

请问一条存储过程