就差最后一句不一样,如果不需要对数据集操作,可以把重复的语句赋给一个字符串变量,用EXECUTE执行
if exists (select * from sysobjects where id = object_id('upCritList'))
drop procedure upCritList
GO Create procedure upCritList
@Type int=1,
@RecId int=null
as set nocount off
Declare @sql varchar(500)
Select @sql = 'Select top 50
a.userId
,a.WorkId
,isNull(rtrim(c.Title),'''') as Title
...
Left Join (select a.userId,rating = sum(b.rating)
from works a inner join critiques b on a.workId=b.workId
group by a.userId) e on a.userId=e.userId '
if @Type = 1
begin
@sql = @sql+'where a.WorkId ='+ @RecId
end
else
begin
@sql = @sql+'where a.userId ='+ @RecId
end Execute(@sql)
if exists (select * from sysobjects where id = object_id('upCritList'))
drop procedure upCritList
GO Create procedure upCritList
@Type int=1,
@RecId int=null
as set nocount off
Declare @sql varchar(500)
Select @sql = 'Select top 50
a.userId
,a.WorkId
,isNull(rtrim(c.Title),'''') as Title
...
Left Join (select a.userId,rating = sum(b.rating)
from works a inner join critiques b on a.workId=b.workId
group by a.userId) e on a.userId=e.userId '
if @Type = 1
begin
@sql = @sql+'where a.WorkId ='+ @RecId
end
else
begin
@sql = @sql+'where a.userId ='+ @RecId
end Execute(@sql)
解决方案 »
- 求查询函数
- 求一个存储过程,主要功能为所有用户money字段加一个外面传进来的一个int参数
- SqlServer2000中如何跟踪客户端的操作
- 关于sp_configure的问题,求解?
- 关于SQL语句like的修改方法~在线等!
- SELECT查询语句无法执行,提示:“在执行批处理时出现错误。错误消息为: 目录名无效。”
- 请教个一直没有预见的问题。。都是理论+实际的常识
- text 型我怎么判断其是否为空???
- 不是你想象中那么简单,请问如何在存储过程中取出某月的第一天'YYYY-MM-01'
- 如何实现这样的功能:将多个表格相同定义的字段连接成一个新表,或者生成一个视图?哪种方法更好些?
- 求救SQL语句,在线等待
- 超级难题!!!
where (@Type=1 and a.userId = @RecId) or (@Type<>1 and a.WorkId = @RecId)