sp_executesql 与表变量

Question

CREATE PROC sp_FenYe_news
@startIndex int,
@endIndex int,
@where nvarchar(1000),
@count int output
AS
SET NOCOUNT ON
DECLARE @indextable table([id] int identity(1,1),nid int)--声明一个表变量
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
   SET @sql='INSERT INTO  @table (nid) SELECT [id] FROM news WHERE'+ @where
   EXEC SP_EXECUTESQL @sql,N'@where',@where
 END
ELSE
  INSERT INTO @indextable(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from @indextable
SELECT * FROM news n
INNER JOIN @indextable t ON n.[id]=t.nid 
WHERE t.[id] between @startIndex and @endIndex ORDER BY t.[id] DESC
SET NOCOUNT OFF
这是一个分页的存储过程 ，现在我只要一执行就出现@indextable没有定义，
我现在知道是因为@indextable是一个表变量的原因，搜了一些方法仍然无解，请大侠帮助一下小妹

Answer 1

改成临时表：
CREATE PROC sp_FenYe_news
@startIndex int,
@endIndex int,
@where nvarchar(1000),
@count int output
AS
SET NOCOUNT ON
create table #indextable ([id] int identity(1,1),nid int)--声明一个表变量
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
  SET @sql='INSERT INTO #indextable (nid) SELECT [id] FROM news WHERE'+ @where
  EXEC SP_EXECUTESQL @sql,N'@where',@where
 END
ELSE
  INSERT INTO #indextable(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from #indextable
SELECT * FROM news n
INNER JOIN #indextable t ON n.[id]=t.nid  
WHERE t.[id] between @startIndex and @endIndex ORDER BY t.[id] DESC
DROP TABLE #indextableSET NOCOUNT OFF

Answer 2

CREATE PROC sp_FenYe_news
@startIndex int,
@endIndex int,
@where nvarchar(1000),
@count int output
AS
SET NOCOUNT ON
create table #t([id] int identity(1,1),nid int)--创建一个临时表
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
  SET @sql='INSERT INTO #t (nid) SELECT [id] FROM news WHERE '+ @where
  EXEC SP_EXECUTESQL @sql,N'@where nvarchar(1000)',@where
 END
ELSE
  INSERT INTO #t(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from #t
SELECT * 
FROM news n
INNER JOIN #t t ON n.[id]=t.nid  
WHERE t.[id] between @startIndex and @endIndex 
ORDER BY t.[id] DESC
SET NOCOUNT OFF

Answer 3

别用表变量了，用临时表。
SET NOCOUNT ON
CREATE table #indextable([id] int identity(1,1),nid int)--声明一个表变量
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
  SET @sql='INSERT INTO #indextable (nid) SELECT [id] FROM news WHERE'+ @where
  EXEC SP_EXECUTESQL @sql,N'@where',@where
 END
ELSE
  INSERT INTO @indextable(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from @indextable
SELECT * FROM news n
INNER JOIN @indextable t ON n.[id]=t.nid  
WHERE t.[id] between @startIndex and @endIndex ORDER BY t.[id] DESC
SET NOCOUNT OFF

Answer 4

EXEC SP_EXECUTESQL @sql,N'@where',@where
==》
EXEC SP_EXECUTESQL @sql

Answer 5

@indextab
把这个改为临时表.

Answer 6

--改动地方：
--1.修改表变量为临时表
--2.在SET @sql='INSERT INTO #t (nid) SELECT [id] FROM news WHERE '的WHERE后面加个空格
--3.EXEC SP_EXECUTESQL 后面的@where没有声明类型
--因临时表在过程执行完后会自动消失，所以没有DROP。

Answer 7

测试结果没问题，lz给点测试数据和news结构
CREATE PROC sp_FenYe_news
@startIndex int,
@endIndex int,
@where nvarchar(1000),
@count int output
AS
SET NOCOUNT ON
DECLARE @indextable table([id] int identity(1,1),nid int)--声明一个表变量
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
  SET @sql='INSERT INTO @table (nid) SELECT [id] FROM news WHERE'+ @where
  EXEC SP_EXECUTESQL @sql,N'@where',@where
 END
ELSE
  INSERT INTO @indextable(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from @indextable
SELECT * FROM news n
INNER JOIN @indextable t ON n.[id]=t.nid  
WHERE t.[id] between @startIndex and @endIndex ORDER BY t.[id] DESC
SET NOCOUNT OFF
create table news(id int identity(1,1),name varchar(10))
insert into news  select 'aaa'exec sp_FenYe_news  2,6,'',1id          name       id          nid         
----------- ---------- ----------- ----------- 
6           aaa        6           6
5           aaa        5           5
4           aaa        4           4
3           aaa        3           3
2           aaa        2           2

Answer 8

10楼，你传@where的参数为空，当然没问题，问题就出现在不为空的情况

Answer 9

declare @tablename varchar(30)
declare @sql varchar(100)
set @tablename='sc'
set @sql='select * from '+@tablename
exec (@sql)

Answer 10

 --sp_executesql不支持传入表变量

Answer 11

没仔细看，这回好了，还改了别的问题
create PROC sp_FenYe_news
@startIndex int,
@endIndex int,
@where nvarchar(1000),
@count int output
AS
SET NOCOUNT ON
create table #indextable ([id] int identity(1,1),nid int)--声明一个表变量
DECLARE @sql AS NVARCHAR(4000)
SET ROWCOUNT @endIndex
IF(LEN(@where)>0 AND @where is not null)--判断条件是不是为空
 BEGIN   
  SET @sql='INSERT INTO #indextable (nid) SELECT [id] FROM news WHERE '+ @where
  EXEC SP_EXECUTESQL @sql--,N'@where',@where
 END
ELSE
  INSERT INTO #indextable(nid) SELECT [id] FROM news
SELECT @count=COUNT([id]) from #indextable
SELECT * FROM news n
INNER JOIN #indextable t ON n.[id]=t.nid  
WHERE t.[nid]/*改了一下*/ between @startIndex and @endIndex ORDER BY t.[id] DESC
SET NOCOUNT OFF
create table news(id int identity(1,1),name varchar(10))
insert into news  select 'aaa'exec sp_FenYe_news  2,6,'id>5',1id          name       id          nid         
----------- ---------- ----------- ----------- 
6           aaa        1           6