存储过程出错，必须声明变量@indextable

初试的存储过程:

CREATE PROCEDURE Pro_GetProProject_By_QueryStr
@CateID int,
@SearchType nvarchar(50),
@SearchKey nvarchar(50),
@PageSize int,
@PageIndex int,
@DoCount bit
as
set nocount on
if(@DoCount=1)
select count(ProjectID) from proProject
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select ProjectID from proProject order by ProjectID desc

declare @SearchString nvarchar(255)
declare @SearchSql nvarchar(2000)

set @SearchString = CASE @SearchType
                            when '1'  then '1 = 1'
                            when '2'  then  'p.ProjectName like ''' + '%'  +  @searchkey + '%'  + ''''
                            when '3'  then  'p.ProjectCity like ''' + '%'+ @searchkey + '%' +''''
                            when '4'  then  'c.CateName like '''  + '%' + @searchkey + '%' + ''''
                            when '4'  then  'p.ProjectManager like ''' + '%' + @searchkey + '%' +''''
                            END

set @SearchSql = N'

SELECT p.*,datename(year,ProjectPostTime)+ '   +  '''-''' + ' + datename(month,ProjectPostTime)+ '+ '''-''' + ' + datename(day,ProjectPostTime)' + ' as PostTime, m.EmpName,c.CateName FROM proProject As p ,mrBaseInf As m ,proCate c,@indextable t WHERE p.EmpID = m.EmpID and p.CateID = c.CateID  and '
 
 + @SearchString

+ '  AND p.CateID = @CateID
        AND  p.ProjectID = t.nid
        AND  t.id>@PageLowerBound and t.id<= @PageUpperBound order by t.id'

print(@SearchSql)

exec(@SearchSql)

end
set nocount off
GO

出错原因:
必须把申明临时表的语句放在 exec(@SQL) 中的 @SQL语句里面一起执行:

declare @str varchar(1000)
set @str='declare @indextable table(id int identity(1,1),nid int)  ' + 其他使用临时表的语句

修改后:
CREATE  PROCEDURE Pro_GetProProject_By_QueryStr
@CateID int,
@SearchType nvarchar(50),
@SearchKey nvarchar(50),
@PageSize int,
@PageIndex int,
@DoCount bit
as
set nocount on
if(@DoCount=1)
select count(ProjectID) from proProject
else
begin

declare @SearchString nvarchar(255)
declare @SearchSql nvarchar(2000)

set @SearchString = CASE @SearchType
                            when '1'  then '1 = 1'
                            when '2'  then  'p.ProjectName like ''' + '%'  +  @searchkey + '%'  + ''''
                            when '3'  then  'p.ProjectCity like ''' + '%'+ @searchkey + '%' +''''
                            when '4'  then  'c.CateName like '''  + '%' + @searchkey + '%' + ''''
                            when '4'  then  'p.ProjectManager like ''' + '%' + @searchkey + '%' +''''
                            END

set @SearchSql = N'

declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(' + cast(@pageindex as nvarchar) + '-1)*' + cast(@pagesize as nvarchar) + '
set @PageUpperBound=@PageLowerBound+' + cast(@pagesize as nvarchar) + '
set rowcount @PageUpperBound
insert into @indextable(nid) select ProjectID from proProject order by ProjectID desc

SELECT p.*,datename(year,ProjectPostTime)+ '   +  '''-''' + ' + datename(month,ProjectPostTime)+ '+ '''-''' + ' + datename(day,ProjectPostTime)' + ' as PostTime, m.EmpName,c.CateName FROM proProject As p ,mrBaseInf As m ,proCate c,@indextable t WHERE p.EmpID = m.EmpID and p.CateID = c.CateID  and '
 
 + @SearchString

+ '  AND p.CateID = ' + cast(@CateID as nvarchar) + '
        AND  p.ProjectID = t.nid
        AND  t.id>@PageLowerBound and t.id<= @PageUpperBound order by t.id'

print(@SearchSql)

exec(@SearchSql)

end
set nocount off
GO