CREATE PROCEDURE Pages
@startIndex int='', --分页开始标记
@endIndex int='', --分页结束标记
@SQL varchar(8000)=''
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID desc
SELECT * FROM (sp_executesql @SQL) c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id desc
end这段代码老是提示 @SQL附近有语法错误,请各位帮忙看一下是怎么回事,谢谢!
@startIndex int='', --分页开始标记
@endIndex int='', --分页结束标记
@SQL varchar(8000)=''
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID desc
SELECT * FROM (sp_executesql @SQL) c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id desc
end这段代码老是提示 @SQL附近有语法错误,请各位帮忙看一下是怎么回事,谢谢!
@startIndex int null, --分页开始标记
@endIndex int null, --分页结束标记
@SQL varchar(8000) null
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID desc
SELECT * FROM (sp_executesql @SQL) c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id desc
end
@startIndex int=0, --分页开始标记
@endIndex int=0, --分页结束标记
-- 或者用成对应类型的默认值看看。
像INT的默认值应该设置为0
这
@startIndex int , --分页开始标记
@endIndex int , --分页结束标记
@SQL varchar(8000)
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID desc
select * from ((sp_executesql @SQL)k) c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id desc
end我改成这样后,还是提示 错误170:第11行:'@SQL'附近有语法错误SQL 2000
CREATE PROCEDURE Pages
@startIndex int='', --分页开始标记
@endIndex int='', --分页结束标记
@SQL varchar(8000)=''
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID descdeclare @c table(....)
insert into @c exec (@sql)
SELECT * FROM @c c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id desc
end
问题已解决,代码贴出来:
CREATE PROCEDURE Pages
@startIndex int='', --分页开始标记
@endIndex int='', --分页结束标记
@SQL varchar(8000)=''
AS
begin
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select OrderID from [order details] order by OrderID asccreate table tmp(
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Discount] [real] NOT NULL ,
[LastEditDate] [datetime] NULL ,
[CreationDate] [datetime] NULL
)
insert into tmp exec (@SQL) SELECT * FROM tmp c inner join @indextable t on
c.OrderID=t.nid
where t.id between @startIndex and @endIndex
order by t.id descdrop table tmp
end
GO