老是语法通不过去。搞了非常久。我知道在组合那些参数的时候错误。唉,不熟练啊。老是错误。
哪位帮我调试下。
ALTER proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字@KeyIndex int,--关键字生效区域@IsVip int, --只查看高级会员@startIndex int,
@endIndex int,
@docount bit)as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')' if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'if(@IsVip<>1985)
set @strFilter=@strFilter+' and (RoleID=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) if(@docount=1)
exec
('
select count(bb.SawID) from
(
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='+@Key @strFilter +'UNION
SELECT SawID
FROM TaskCompanyAdsKeyWHERE 1=1 and (SawAdsKey <> '+@Key+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+'
)bb
')
else
begin
exec
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid)
select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='''+@Key @strFilter+'UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+@Key+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.*from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+@startIndex+' and t.id<='+@endIndex+'
') end
set nocount off
哪位帮我调试下。
ALTER proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字@KeyIndex int,--关键字生效区域@IsVip int, --只查看高级会员@startIndex int,
@endIndex int,
@docount bit)as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')' if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'if(@IsVip<>1985)
set @strFilter=@strFilter+' and (RoleID=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) if(@docount=1)
exec
('
select count(bb.SawID) from
(
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='+@Key @strFilter +'UNION
SELECT SawID
FROM TaskCompanyAdsKeyWHERE 1=1 and (SawAdsKey <> '+@Key+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+'
)bb
')
else
begin
exec
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid)
select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='''+@Key @strFilter+'UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+@Key+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.*from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+@startIndex+' and t.id<='+@endIndex+'
') end
set nocount off
哪位帮我调试下。
ALTER proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) exec
('
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='+@Key @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '+@Key+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb
') else begin
exec
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid)
select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+@KeyIndex+' and SawAdsKey ='''+@Key @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+@Key+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+@startIndex+' and t.id <='+@endIndex+'
') end set nocount off -------------------
这两个@Key @strFilter 之间缺少符号连接.
或者使用一个字符串函数转换.
这个存储过程比较不好写。都不知道要怎么转换才好
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) exec
--print
('
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='+ltrim(@Key) + @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '+ltrim(@Key)+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+ @strFilter+' )bb
') else begin
exec
--print
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key) + @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+ltrim(@Key)+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
') end set nocount off
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类@CompanyJY nvarchar(50),--经营分类,采用Text属性@DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字@KeyIndex int,--关键字生效区域@IsVip int, --只查看高级会员@startIndex int,
@endIndex int,
@docount bit)as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''if(@BigCityID<>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID<>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')' if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'if(@IsVip<>1985)
set @strFilter=@strFilter+' and (RoleID=1)'if(@DateNum<>1985 and @DateNum<>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) if(@docount=1)
exec
('
select count(bb.SawID) from
(
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+cast(@KeyIndex as nvarchar)+' and SawAdsKey ='+@Key+ @strFilter +'UNION
SELECT SawID
FROM TaskCompanyAdsKeyWHERE 1=1 and (SawAdsKey <> '+@Key+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+'
)bb
')
else
begin
exec
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid)
select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+Cast(@KeyIndex as nvarchar)+' and SawAdsKey ='''+@Key + @strFilter+'UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+@Key+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.*from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+@startIndex+' and t.id<='+@endIndex+'
') end
set nocount off
============
cast附件语法错误
SawAdsKey <>'+ltrim(@Key)+'
换成
SawAdsKey <>'''+ltrim(@Key)+'''即在 @key 变量上加上引号,
create proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) exec
--print
('
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='+ltrim(@Key) + @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '+ltrim(@Key)+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+ @strFilter+' )bb
') else begin
exec
--print
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key) + @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+ltrim(@Key)+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
') end set nocount off
==================
lterim附件语法错误
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) exec
--print
('
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key) +''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+ltrim(@Key)+''' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+ @strFilter+' )bb
') else begin
exec
--print
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key) +''' '+ @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'''+ltrim(@Key)+''' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
') end set nocount off
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) exec
--print
('
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key)+''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+ltrim(@Key)+''' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+ @strFilter+' )bb
') else begin
exec
--print
(' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key)+''' '+ @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'''+ltrim(@Key)+''' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
') end set nocount off
===============================
ltrim附件语法错误
exec
--print
换成
--exec
print
打印出来看看~
--drop proc dbo.GetCompanyList_Search
--go
create proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
declare @strSQL nvarchar(4000)
set @strFilter=''
set @strSQL =''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) --exec
--print
set @strSQL =
'
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+convert(nvarchar(40),@KeyIndex)+' and SawAdsKey ='''+@Key+''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+@Key+''' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb
' else begin
--exec
--print
set @strSQL =
' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+convert(nvarchar(40),@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key)+''' '+ @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'''+ltrim(@Key)+''' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+convert(nvarchar(40),@startIndex)+' and t.id <='+convert(nvarchar(40),@endIndex)+'
' end exec (@strSQL)set nocount off
select count(bb.SawID) from
(
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage=1 and SawAdsKey ='key'
and ( BigCityID=1)
and ( SmallCityID=2)
and (CompanyJY = 'CompanyJY')
and (RoleID=1)
and RegDate>=18/06/08 UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> 'key' or SawAdsKey is Null )
and AND (CompanyName LIKE '%key%' OR
ProdList LIKE '%key%') and ( BigCityID=1) and ( SmallCityID=2) and (CompanyJY = 'CompanyJY') and (RoleID=1) and RegDate>=18/06/08 )bb */
多了一个AND
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+convert(nvarchar(40),@KeyIndex)+' and SawAdsKey ='''+@Key+''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+@Key+''' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb
去掉这个 'AND'.
运行[dbo].[GetCompanyList_Search] ( @BigCityID = 1985, @SmallCityID = 1985, @CompanyJY = , @DateNum = 1985, @Key = 刀具, @KeyIndex = 1, @IsVip = 1985, @startIndex = 1, @endIndex = 25, @docount = true ).在关键字 'AND' 附近有语法错误。
--drop proc dbo.GetCompanyList_Search
--go
create proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
declare @strSQL nvarchar(4000)
set @strFilter=''
set @strSQL =''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) --exec
--print
set @strSQL =
'
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+@Key+''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+@Key+''' or SawAdsKey is Null ) and (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb
' else begin
--exec
--print
set @strSQL =
' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key)+''' '+ @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'''+ltrim(@Key)+''' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
' end exec(@strSQL)set nocount off
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage=1 and SawAdsKey ='刀具' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'刀具' or SawAdsKey is Null ) AND (CompanyName LIKE '%刀具%' OR
ProdList LIKE '%刀具%')
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>=1 and t.id <=25必须声明变量 '@endIndex'。
ALTER proc GetCompanyList_Search
(
@BigCityID int,--城市大分类
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bit) as
set nocount on
declare @strFilter nvarchar(800)
declare @strSQL nvarchar(4000)
set @strFilter=''
set @strSQL =''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3)
if(@docount=1) --exec
--print
set @strSQL =
'
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+@Key+''' '+ @strFilter +' UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '''+@Key+''' or SawAdsKey is Null ) and (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb
' else begin
--exec
--print
set @strSQL =
' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+ltrim(@KeyIndex)+' and SawAdsKey ='''+ltrim(@Key)+''' '+ @strFilter+' UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'''+ltrim(@Key)+''' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+ltrim(@Key)+'%'' OR
ProdList LIKE ''%'+ltrim(@Key)+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+ltrim(@startIndex)+' and t.id <='+ltrim(@endIndex)+'
' end exec(@strSQL)set nocount off
改成
set rowcount '+ltrim(@endIndex)+'
这句的问题,
@SmallCityID int,--城市小分类 @CompanyJY nvarchar(50),--经营分类,采用Text属性 @DateNum int, --间隔天数 @Key nvarchar(60), --公司名称关键字 @KeyIndex int,--关键字生效区域 @IsVip int, --只查看高级会员 @startIndex int,
@endIndex int,
@docount bitas
set nocount on
declare @strFilter nvarchar(800)
set @strFilter=''
if(@BigCityID <>1985)
set @strFilter=@strFilter+ ' and ( BigCityID='+CAST (@BigCityID AS NVarChar)+')' if(@SmallCityID <>1985)
set @strFilter=@strFilter+' and ( SmallCityID='+CAST (@SmallCityID AS NVarChar)+')'
if(@CompanyJY <>'')
set @strFilter=@strFilter+' and (CompanyJY = '''+@CompanyJY +''')'
if(@IsVip <>1985)
set @strFilter=@strFilter+' and (RoleID=1)' if(@DateNum <>1985 and @DateNum <>0)
set @strFilter=@strFilter+' and RegDate>='+CONVERT(char(12),dateadd(d,-@DateNum,GetDate()), 3) declare @sqltext nvarchar(1000)
if(@docount=1)
begin
set @sqltext=
'
select count(bb.SawID) from
( SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+convert(nvarchar,@keyindex)+' and SawAdsKey ='+@Key+ @strFilter +' --此处改过UNION
SELECT SawID
FROM TaskCompanyAdsKey WHERE 1=1 and (SawAdsKey <> '+@Key+' or SawAdsKey is Null ) and AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+ @strFilter+' )bb '
exec(@sqltext)
end
else begin
set @sqltext=
' declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select bb.SawID
from (
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and SawSearchPage='+convert(nvarchar,@KeyIndex)+' and SawAdsKey ='''+@Key + @strFilter+' --此处改过UNION
SELECT SawID
FROM TaskCompanyAdsKey
WHERE 1=1 and (SawAdsKey <>'+@Key+' or SawAdsKey is Null ) AND (CompanyName LIKE ''%'+@Key+'%'' OR
ProdList LIKE ''%'+@Key+'%'')'+@strFilter+'
) bb
select TaskCompanyAdsKey.* from TaskCompanyAdsKey
inner join @indextable t
on TaskCompanyAdsKey.SawID=t.nid
where t.id>='+@startIndex+' and t.id <='+@endIndex+'
'
exec(@sqltext)end set nocount off