sp_executesql 与表变量

[arjsyy]

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是一个表变量的原因，搜了一些方法仍然无解，请大侠帮助一下小妹

[chuifengde]

改成临时表：
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 #indextable

SET NOCOUNT OFF

[htl258_Tony]

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

[Zoezs]

别用表变量了，用临时表。

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

[chuifengde]

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

[dawugui]

@indextab
把这个改为临时表.

[htl258_Tony]

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

[亮剑_]

1楼、2楼答案雷同，回帖时间也雷同
雷

[arjsyy]

等下问题解决了就结贴去了

[yujunlin32167]

[Quote=引用 7 楼 tjianliang 的回复:]
1楼、2楼答案雷同，回帖时间也雷同
雷
[/Quote]
这叫心有灵犀一点通..

[永生天地]

测试结果没问题，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,'',1

id          name       id          nid         
----------- ---------- ----------- ----------- 
6           aaa        6           6
5           aaa        5           5
4           aaa        4           4
3           aaa        3           3
2           aaa        2           2

[chuifengde]

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

[sych888]

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

[--小F--]

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

说得够清晰了

[sych888]

--sp_executesql不支持传入表变量

[永生天地]

没仔细看，这回好了，还改了别的问题

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',1

id          name       id          nid         
----------- ---------- ----------- ----------- 
6           aaa        1           6