CREATE PROCEDURE UserSearchResultShow @pagesize int,@pageindex int,@docount bit ,@flag int,@GroupID varchar(10),@UserCount varchar(20),@Email varchar(100),@UserName nvarchar(50), @IP varchar(50) AS Declare @v_sql varchar(4000) set nocount on
if(@docount=1) begin if(@UserCount='0') set @v_sql='select * from Usersheet' else set @v_sql='select Top ' + @UserCount + ' * from UserSheet' if(@flag=1 and @UserName<>'') set @v_sql=@v_sql+' where UserName='''+@UserName+'''' else if (@flag=0 and @UserName<>'') set @v_sql=@v_sql + ' where UserName like ''%' + @UserName + '%''' if(@GroupID<>'0' and @UserName<>'') set @v_sql=@v_sql + ' and GroupID=''' + @GroupID+'''' else if(@GroupID<>'0' and @UserName='') set @v_sql=@v_sql + ' where GroupID=''' + @GroupID+'''' if(@Email<>'' and @GroupID='0' and @UserName='') set @v_sql=@v_sql + ' where Email like ''%' + @Email + '%''' else if(@Email<>'' ) set @v_sql=@v_sql + ' and Email like ''%' + @Email + '%''' if(@IP<>'' and @Email='' and @UserName='' and @GroupID='0') set @v_sql=@v_sql + ' where IP like ''%' + @IP + '%''' else if(@IP<>'') set @v_sql=@v_sql + ' and IP like ''%' + @IP + '%''' Exec(@v_sql) end else begin create table #indextable (roomid int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBound set @v_sql='insert into #indextable (nid) '--这句有错误,其余的均无错,错误137:必须声明变量@indextable if(@UserCount='0') set @v_sql=@v_sql+' select UserID from Usersheet' else set @v_sql=@v_sql+ ' select Top ' + @UserCount + ' UserID from UserSheet' if(@flag=1 and @UserName<>'') set @v_sql=@v_sql+' where UserName='''+@UserName+'''' else if (@flag=0 and @UserName<>'') set @v_sql=@v_sql + ' where UserName like ''%' + @UserName + '%''' if(@GroupID<>'0' and @UserName<>'') set @v_sql=@v_sql + ' and GroupID=''' + @GroupID+'''' else if(@GroupID<>'0' and @UserName='') set @v_sql=@v_sql + ' where GroupID=''' + @GroupID+'''' if(@Email<>'' and @GroupID='0' and @UserName='') set @v_sql=@v_sql + ' where Email like ''%' + @Email + '%''' else if(@Email<>'' ) set @v_sql=@v_sql + ' and Email like ''%' + @Email + '%''' if(@IP<>'' and @Email='' and @UserName='' and @GroupID='0') set @v_sql=@v_sql + ' where IP like ''%' + @IP + '%''' else if(@IP<>'') set @v_sql=@v_sql + ' and IP like ''%' + @IP + '%' Exec(@v_sql) select O.* from UserSheet O,#indextable t where O.UserID=t.nid and t.roomid>@PageLowerBound and t.roomid<=@PageUpperBound order by t.roomid end set nocount off GO
这是因为表变量的生命存在周期决定的楼上的: 将表变量的声明也放在动态语句里
可以暂时解决 但看到楼主在后面还用到了那个表变量 @indextable
,这样使用同样存在问题建议:用全局临时表 ##indextable代替,用了之后再drop 掉
@pagesize int,@pageindex int,@docount bit ,@flag int,@GroupID varchar(10),@UserCount varchar(20),@Email varchar(100),@UserName nvarchar(50),
@IP varchar(50)
AS
Declare @v_sql varchar(4000)
set nocount on
if(@docount=1)
begin
if(@UserCount='0')
set @v_sql='select * from Usersheet'
else
set @v_sql='select Top ' + @UserCount + ' * from UserSheet'
if(@flag=1 and @UserName<>'')
set @v_sql=@v_sql+' where UserName='''+@UserName+''''
else if (@flag=0 and @UserName<>'')
set @v_sql=@v_sql + ' where UserName like ''%' + @UserName + '%'''
if(@GroupID<>'0' and @UserName<>'')
set @v_sql=@v_sql + ' and GroupID=''' + @GroupID+''''
else if(@GroupID<>'0' and @UserName='')
set @v_sql=@v_sql + ' where GroupID=''' + @GroupID+''''
if(@Email<>'' and @GroupID='0' and @UserName='')
set @v_sql=@v_sql + ' where Email like ''%' + @Email + '%'''
else if(@Email<>'' )
set @v_sql=@v_sql + ' and Email like ''%' + @Email + '%'''
if(@IP<>'' and @Email='' and @UserName='' and @GroupID='0')
set @v_sql=@v_sql + ' where IP like ''%' + @IP + '%'''
else if(@IP<>'')
set @v_sql=@v_sql + ' and IP like ''%' + @IP + '%'''
Exec(@v_sql)
end
else
begin
create table #indextable (roomid int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
set @v_sql='insert into #indextable (nid) '--这句有错误,其余的均无错,错误137:必须声明变量@indextable if(@UserCount='0')
set @v_sql=@v_sql+' select UserID from Usersheet'
else
set @v_sql=@v_sql+ ' select Top ' + @UserCount + ' UserID from UserSheet'
if(@flag=1 and @UserName<>'')
set @v_sql=@v_sql+' where UserName='''+@UserName+''''
else if (@flag=0 and @UserName<>'')
set @v_sql=@v_sql + ' where UserName like ''%' + @UserName + '%'''
if(@GroupID<>'0' and @UserName<>'')
set @v_sql=@v_sql + ' and GroupID=''' + @GroupID+''''
else if(@GroupID<>'0' and @UserName='')
set @v_sql=@v_sql + ' where GroupID=''' + @GroupID+''''
if(@Email<>'' and @GroupID='0' and @UserName='')
set @v_sql=@v_sql + ' where Email like ''%' + @Email + '%'''
else if(@Email<>'' )
set @v_sql=@v_sql + ' and Email like ''%' + @Email + '%'''
if(@IP<>'' and @Email='' and @UserName='' and @GroupID='0')
set @v_sql=@v_sql + ' where IP like ''%' + @IP + '%'''
else if(@IP<>'')
set @v_sql=@v_sql + ' and IP like ''%' + @IP + '%'
Exec(@v_sql)
select O.* from UserSheet O,#indextable t where O.UserID=t.nid
and t.roomid>@PageLowerBound and t.roomid<=@PageUpperBound order by t.roomid
end
set nocount off
GO