日期:2014-05-17  浏览次数:20402 次

请教一条存储过程
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