CREATE procedure Pager_ViewTable
(@pagesize int,
@pageindex int,
@docount bit,
@tn char(10),
@ghdwname varchar)
as
set nocount ondeclare @t table(name char(10))
set @t = @tn ----------------------------------------------这里报错!!!!!
if(@docount=1)
select count(id) from @t where ghdw=@ghdwname
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 id from @t where ghdw=@ghdwname order by id desc
select O.* from @t O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
(@pagesize int,
@pageindex int,
@docount bit,
@tn char(10),
@ghdwname varchar)
as
set nocount ondeclare @t table(name char(10))
set @t = @tn ----------------------------------------------这里报错!!!!!
if(@docount=1)
select count(id) from @t where ghdw=@ghdwname
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 id from @t where ghdw=@ghdwname order by id desc
select O.* from @t O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
@t = @tn ----------------------------------------------这里报错!!!!!@t 是表变量,@tn是字符型变量,根本不能这样赋值
表变量也不能进行赋值操作,只能用sql语句像操作普通表那样操作表变量
@pagesize int,
@pageindex int,
@docount bit,
@tn char(10),
@ghdwname varchar
as
set nocount on
declare @s nvarchar(4000)if(@docount=1)
begin
set @s=N'select count(id) from '
+quotename(@tn)
+N' where ghdw=@ghdwname'
exec sp_executesql @s,
N'@ghdwname varchar',
@ghdwname
end
else
begin
set @s=N'
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 id from '
+quotename(@tn)
+N' where ghdw=@ghdwname order by id desc
select O.* from '
+quotename(@tn)
+N' O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id'
exec sp_executesql @s,
N'@pagesize int,@pageindex int,@ghdwname varchar',
@pagesize,@pageindex,@ghdwname
end
set nocount off
GO
作用范围,
你必须在存储过程内部declare他才行.
(@pagesize int,
@pageindex int,
@docount bit,
@tn char(10),
@ghdwname varchar)
as
set nocount ondeclare @t table(name char(10))
--@t = @tn ----------------------------------------------这里报错!!!!!
set @t = @tn 改正
if(@docount=1)
select count(id) from @t where ghdw=@ghdwname
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 id from @t where ghdw=@ghdwname order by id desc
select O.* from @t O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
GO
SQLSERVER中给变量赋值要用select 或者set