以下是一个根据不同情况条件查询相应信息的存储过程,总觉得用if else 很没意思,怎么做条件查询才好呢,这些代码又怎么优化?高手指点一下好吗?代码如下:
create proc getList @title varchar(20),@userID int,@time varchar(20),@Tsid int
as
if(@title!='' and @time!='' and @Tsid!=0)
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID and Tsid=@Tsid
end
if(@title!='' and @time!='' and @Tsid=0)
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID
end
if(@title!='' and @Tsid!=0 and @time='')
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and Tsid=@Tsid and Tuid=@userID
end
if(@title!='' and @Tsid=0 and @time='')
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and Tuid=@userID
end
if(@title='' and @Tsid!=0 and @time!='')
begin
select * from bbsTopic where convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tsid=@Tsid and Tuid=@userID
end
if(@title='' and @Tsid=0 and @time!='')
begin
select * from bbsTopic where convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID
end
if(@title='' and @Tsid!=0 and @time='')
begin
select * from bbsTopic where Tsid=@Tsid and Tuid=@userID
end
if(@title='' and @time='' and @Tsid=0 and @userID!=0)
begin
select * from bbsTopic where Tuid=@userID
end
GO
create proc getList @title varchar(20),@userID int,@time varchar(20),@Tsid int
as
if(@title!='' and @time!='' and @Tsid!=0)
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID and Tsid=@Tsid
end
if(@title!='' and @time!='' and @Tsid=0)
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID
end
if(@title!='' and @Tsid!=0 and @time='')
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and Tsid=@Tsid and Tuid=@userID
end
if(@title!='' and @Tsid=0 and @time='')
begin
select * from bbsTopic where Ttopic like '%'+@title+'%' and Tuid=@userID
end
if(@title='' and @Tsid!=0 and @time!='')
begin
select * from bbsTopic where convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tsid=@Tsid and Tuid=@userID
end
if(@title='' and @Tsid=0 and @time!='')
begin
select * from bbsTopic where convert(varchar(20),Ttime,120) like '%'+@time+'%' and Tuid=@userID
end
if(@title='' and @Tsid!=0 and @time='')
begin
select * from bbsTopic where Tsid=@Tsid and Tuid=@userID
end
if(@title='' and @time='' and @Tsid=0 and @userID!=0)
begin
select * from bbsTopic where Tuid=@userID
end
GO
第2個開始 else if 才對 吧
这里写else if和写if是一样的
create proc getList
@title varchar(20)=null,
@userID int=null,
@time datetime=null,
@Tsid int=null
as
declare @sql nvarchar(4000)
set @sql=N'select * from bbsTopic where 1=1 '
+case when @title is not null then
N' and Ttopic like %@title%' else N'' end
+case when @userID is not null then
N'Tuid=@userID 'else N'' end
+case when @time is not null then
N'datediff(ss,Ttime,@time)=0' else N'' end
+case when @Tsid is not null then
N'Tsid=@Tsid' else N'' end
exec sp_executesql
@sql,
N'@title as varchar(20),@userID as int,@time as datetime,@Tsid as int ',
@title=@title,
@userID =@userID ,
@time=@time,
@Tsid=@Tsid
报错:
消息 102,级别 15,状态 1,第 1 行
'@title' 附近有语法错误。
你写上没 and 前面加点空格
@title varchar(20)=null,
@userID int=null,
@time datetime=null,
@Tsid int=null
as
declare @sql nvarchar(4000)
set @sql=N'select * from bbsTopic where 1=1 '
+case when @title is not null then
N' and Ttopic like %@title%' else N'' end
+case when @time is not null then
N' and datediff(ss,Ttime,@time)=0' else N'' end
+case when @Tsid is not null then
N' and Tsid=@Tsid' else N'' end
exec sp_executesql
@sql,
N'@title as varchar(20),@userID as int,@time as datetime,@Tsid as int ',
@title=@title,
@userID =@userID ,
@time=@time,
@Tsid=@Tsid
GO
我改成这样啦,也加了and加空格啦,还是报那个错:
消息 102,级别 15,状态 1,第 1 行
'@title' 附近有语法错误。
准备下班了
你再看看吧
@title nvarchar(40)=null,
@userID int=null,
@time datetime=null,
@Tsid int=null
as
declare @sql nvarchar(4000)
set @sql=N'select * from bbsTopic where 1=1 '
+case when @title is not null then
N' and Ttopic like ''%''+@title+''%''' else N'' end
+case when @time is not null then
N' and datediff(ss,Ttime,@time)=0' else N'' end
+case when @Tsid is not null then
N' and Tsid=@Tsid' else N'' end
exec sp_executesql
@sql,
N'@title as nvarchar(40),@userID as int,@time as datetime,@Tsid as int ',
@title=@title,
@userID =@userID ,
@time=@time,
@Tsid=@Tsid
GO
显示出是三条数据没错,但是一执行:exec getList2 'java',1,'2010-11-02',1
这个执行明明应该有三条数据的,但却变成了没有数据,汗
create proc getList2
@title nvarchar(40)=null,
@userID int=null,
@time varchar(20)=null,
@Tsid int=null
as
declare @sql nvarchar(4000)
set @sql=N'select * from bbsTopic where 1=1 '
+case when @title is not null then
N' and Ttopic like ''%''+@title+''%''' else N'' end
+case when @time is not null then
N' and convert(varchar(20),Ttime,120) like ''%''+@time+''%''' else N'' end
+case when @Tsid is not null then
N' and Tsid=@Tsid' else N'' end
exec sp_executesql
@sql,
N'@title as nvarchar(40),@userID as int,@time as varchar(20),@Tsid as int ',
@title=@title,
@userID =@userID ,
@time=@time,
@Tsid=@Tsid
GO