写了个存储过程,实现多条件查询和分页,可多条件查询不成功.临时变量@strWhere不起作用!!!!!!!!!
create PROCEDURE dbo.SearchLinksPage
(
@createDate NVarChar(50),
@linkPageKeyword nvarchar(50),
@country nvarchar(50),
@category nvarchar(50),
@linkPageType nvarchar(50),
@pagecount nvarchar(20),
@pagesize nvarchar(20)
)
AS
begin
DECLARE @strWhere nvarchar(500)
if(@createDate <>'')
set @strWhere=@strWhere+' AND createDate='+@createDate
if(@linkPageKeyword <>'')
set @strWhere=@strWhere+' AND linkPageTitle LIKE %'+@linkPageKeyword+'% OR linkPageURL LIKE %'+@linkPageKeyword+'%'
if(@country <>'All Country')
set @strWhere=@strWhere+' AND country='+@country
if(@category <>'All Category')
set @strWhere=@strWhere+' AND linkPageCategory='+@category
if(@linkPageType <>'All Types')
set @strWhere=@strWhere+' AND linkPageType='+@linkPageType
EXEC('SELECT top '+@pagesize+' * FROM SellerLinkPages WHERE state <>0'+@strWhere+' AND sellerLinkPages_ID NOT IN (SELECT TOP '+@pagecount+' sellerLinkPages_ID FROM SellerLinkPages WHERE state <>0'+@strWhere+')')
end
create PROCEDURE dbo.SearchLinksPage
(
@createDate NVarChar(50),
@linkPageKeyword nvarchar(50),
@country nvarchar(50),
@category nvarchar(50),
@linkPageType nvarchar(50),
@pagecount nvarchar(20),
@pagesize nvarchar(20)
)
AS
begin
DECLARE @strWhere nvarchar(500)
if(@createDate <>'')
set @strWhere=@strWhere+' AND createDate='+@createDate
if(@linkPageKeyword <>'')
set @strWhere=@strWhere+' AND linkPageTitle LIKE %'+@linkPageKeyword+'% OR linkPageURL LIKE %'+@linkPageKeyword+'%'
if(@country <>'All Country')
set @strWhere=@strWhere+' AND country='+@country
if(@category <>'All Category')
set @strWhere=@strWhere+' AND linkPageCategory='+@category
if(@linkPageType <>'All Types')
set @strWhere=@strWhere+' AND linkPageType='+@linkPageType
EXEC('SELECT top '+@pagesize+' * FROM SellerLinkPages WHERE state <>0'+@strWhere+' AND sellerLinkPages_ID NOT IN (SELECT TOP '+@pagecount+' sellerLinkPages_ID FROM SellerLinkPages WHERE state <>0'+@strWhere+')')
end
set @strwhere=''
if(@createDate <>'')
set @strWhere=@strWhere+' AND createDate='+@createDate
if(@linkPageKeyword <>'')
set @strWhere=@strWhere+' AND linkPageTitle LIKE %'+@linkPageKeyword+'% OR linkPageURL LIKE %'+@linkPageKeyword+'%'
if(@country <>'All Country')
set @strWhere=@strWhere+' AND country='+@country
if(@category <>'All Category')
set @strWhere=@strWhere+' AND linkPageCategory='+@category
if(@linkPageType <>'All Types')
set @strWhere=@strWhere+' AND linkPageType='+@linkPageType
set @strWhere=N''
declare @strWhere nvarchar(500)
set @strWhere=@strWhere+'asdf'
select @strWhere
/*
NULL
*/