急！！！关于查询条件的一个问题！！！在线等希望大虾们帮我看一下哦！！！

[foollt]

我要写一个搜索的存储过程，里面包括了个个不同的搜索条件，代码如下：
use ekoshop
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

alter Procedure spGetSearchItems
@i_EndDate int,
--@i_Bit int,
@i_title varchar(50)=null,
@i_Zip varchar(50)=null,
@i_UserName varchar(50)=null,
@i_ItemCode char(10)=null,
@i_page int,
@i_pageSize int,
@i_kind bit,
@i_MaxPrice money,
@i_MinPrice money,
@i_Categories int=null,
@o_row_total int=null output,
@o_pageCount int=null output,
@o_RecordCount int=null output
AS
begin
declare @l_Sql varchar(1000)
declare @l_temp varchar(1000)
declare @l_CountBank int
DECLARE @l_Start int --start record
DECLARE @l_End int --end record
DECLARE @l_Page int
DECLARE @l_returncount int
declare @l_CateCode int
declare @l_Dendtime datetime
set nocount on
select --查出第3第4级分类
@l_CateCode=CategoryCode
from
viewCategoryName
where
Sid like '%'+cast(@i_Categories as varchar(50))+'%'
--set @l_temp='select * from TbonlineItem,tbuser where 1=1'
set @l_temp='1=1'
/*if(@i_title is not null)
begin
set @l_temp=@l_temp+ 'and TbonlineItem.title like'+'%'+@i_title+'%'
end*/
if(@i_Zip is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.Zip='+@i_Zip
end
if(@i_username is not null)
begin
set @l_temp=@l_temp+'and Tbuser.username='+@i_Username
end
if(@i_ItemCode is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.ItemCode='+cast(@i_ItemCode as varchar(50))
end
if(@i_MaxPrice is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.price<='+cast(@i_MaxPrice as varchar(50))
end
if(@i_MinPrice is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.price>='+cast(@i_MinPrice as varchar(50))
end
if(@i_Enddate is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.EndTime<dateadd(day,'+cast(@i_EndDate as varchar(50))+',getdate())'
end
set @l_temp=@l_temp+'and TbonlineItem.kind='+cast(@i_kind as varchar(50))+'and TbonlineItem.CategoryCode in'+'('+cast(@l_CateCode as varchar(50))+')'

create table #t --临时表
(ID int IDENTITY,--自增字段
ItemCode char(10),
username varchar(50)
)
set @l_Sql='insert into #t
SELECT
TbonlineItem.ItemCode,
Tbuser.username
from TbonlineItem
left join tbuser
on TbonlineItem.userId=Tbuser.id
where '
set @l_Sql=@l_Sql+@l_temp

exec (@l_Sql)
--确定总页数
if(@o_RecordCount%@i_pageSize = 0)
set @o_pageCount = CEILING(@o_RecordCount/@i_pageSize)
else
set @o_pageCount = CEILING(@o_RecordCount/@i_pageSize) + 1

--若请求的页号大于总页数，则显示最后一页
if(@i_page > @o_pageCount)
set @l_page = @o_pageCount
else
set @l_page = @i_page

--确定当前页的始末记录
set @l_Start = (@l_page - 1) * @i_pageSize
set @l_End = @l_Start + @i_pageSize + 1
select
a.Title,
a.[Description],
b.UserName,
c.ItemImage1,
a.Price,
a.EndTime,
a.kind,
a.categorycode,
a.ItemCode
from TbOnlineItem a
left join TbUser b
On a.UserId=b.Id
left join TbItemImages c
on a.ItemCode=c.ItemCode and a.UserId=c.SellerId
where a.ItemCode in(select ItemCode from #t where ID > @l_Start and ID < @l_End ) order by endTime desc
--确定当前记录数
set @o_RecordCount = @@rowcount
set @o_pageCount = 1
set @l_returncount = @@rowcount
return @o_RecordCount

TRUNCATE TABLE #t
Drop Table #t

end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

有点长，请大虾们帮我看一下，我测试怎么都没有数据显示出来啊！我知道是@l_temp这个出了问题，但我看不出有什么问题！！！请大虾们帮我看一下吧！！！

[OracleRoob]

用查询分析器调试一下

[sxdoujg]

up

[bugchen888]

把exec (@l_Sql)改为：
print @l_Sql
然后执行看结果就知道你构造的SQL语句是否正确了

-------------------------------------------------

完全赞同！！！

[pbsql]

没仔细看完，不过这个：
if(@i_Zip is not null)
begin
set @l_temp=@l_temp+'and TbonlineItem.Zip='+@i_Zip
end
if(@i_username is not null)
begin
set @l_temp=@l_temp+'and Tbuser.username='+@i_Username
end
至少应该改成这样吧：
if(@i_Zip is not null)
begin
set @l_temp=@l_temp+' and TbonlineItem.Zip='''+@i_Zip+''''
end
if(@i_username is not null)
begin
set @l_temp=@l_temp+' and Tbuser.username='''+@i_Username+''''
end
注意and前后应有空格，字符串的连接应有引号

把exec (@l_Sql)改为：
print @l_Sql
然后执行看结果就知道你构造的SQL语句是否正确了