存储过程参数问题求教

kokubo_wing 2006-07-27 10:51:07
select a.*,b.groupno,c.Title,d.KeyPath from AUTH_Answer a right join AUTH_Answer_Access b on a.Answer_ID=b.Answer_ID left join AUTH_GROUP c on b.groupno =c.groupno left join SchoolWork d on a.WorkID = d.WorkID
where b.groupno in ('+GroupNO+') and a.WorkID=d.WorkID
and
datepart(yy,a.Answer_InputDate)=@NowYear
and
datepart(m,a.Answer_InputDate)=@NowMonth
order by c.Title,a.Answer_InputDate desc
现在我的这个GroupNO可能是一个组,也可能是多个组,如果是多个组,传进来的值为"aa,bb,cc"
请问我怎么实现这里where b.groupno in ('aa','bb','cc')?
...全文
203 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
子陌红尘 2006-07-27
  • 打赏
  • 举报
回复
更正一处BUG:

exec sp_executesql @sql,N'@NowYear int,@NowMonth int',@NowYear,@NowMonth
子陌红尘 2006-07-27
  • 打赏
  • 举报
回复
declare @sql nvarchar(4000)

set @sql=N'select a.*,b.groupno,c.Title,d.KeyPath from AUTH_Answer a right join AUTH_Answer_Access b on a.Answer_ID=b.Answer_ID left join AUTH_GROUP c on b.groupno =c.groupno left join SchoolWork d on a.WorkID = d.WorkID
where b.groupno in ('+@GroupNO+N') and a.WorkID=d.WorkID
and
datepart(yy,a.Answer_InputDate)=@NowYear
and
datepart(m,a.Answer_InputDate)=@NowMonth
order by c.Title,a.Answer_InputDate desc'

exec sp_executesql @sql,'@NowYear int,@NowMonth int',@NowYear,@NowMonth
zlp321002 2006-07-27
  • 打赏
  • 举报
回复
--or
select a.*,b.groupno,c.Title,d.KeyPath from AUTH_Answer a right join AUTH_Answer_Access b on a.Answer_ID=b.Answer_ID left join AUTH_GROUP c on b.groupno =c.groupno left join SchoolWork d on a.WorkID = d.WorkID
where ','+GroupNO+',' like '%+b.groupno+%' and a.WorkID=d.WorkID
and
datepart(yy,a.Answer_InputDate)=@NowYear
and
datepart(m,a.Answer_InputDate)=@NowMonth
order by c.Title,a.Answer_InputDate desc
zlp321002 2006-07-27
  • 打赏
  • 举报
回复
select a.*,b.groupno,c.Title,d.KeyPath from AUTH_Answer a right join AUTH_Answer_Access b on a.Answer_ID=b.Answer_ID left join AUTH_GROUP c on b.groupno =c.groupno left join SchoolWork d on a.WorkID = d.WorkID
where charindex(','+b.groupno+',',','+GroupNO+',')>0 and a.WorkID=d.WorkID
and
datepart(yy,a.Answer_InputDate)=@NowYear
and
datepart(m,a.Answer_InputDate)=@NowMonth
order by c.Title,a.Answer_InputDate desc

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧