CREATE PROCEDURE paging@tb as varchar(10), //传入的表名称
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullASdeclare @t_table table
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount @StopRowinsert @t_table
(
callingnumber,callednumber,calltime,duration,callcost
)select callingnumber,truecallednumber,starttime,duration,feefrom @tb //提示没有定义变量where callingnumber like @callingnumber and servicekey=@servicekey
and endtime between @firsttime and @lasttime
and fee<>0
order by starttimeSelect * from @t_table where rownum >=@StartRow
GO
上面是一个分页的存储过程,运行之后提示没有定义变量“@tb”,但是我明明已经在开头定义了这个变量了。如果把参数◎tb直接用表名代替的话,就没有上面的错误。小弟刚刚开始写存储过程,语法方面不是很懂,请大家指教!
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullASdeclare @t_table table
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount @StopRowinsert @t_table
(
callingnumber,callednumber,calltime,duration,callcost
)select callingnumber,truecallednumber,starttime,duration,feefrom @tb //提示没有定义变量where callingnumber like @callingnumber and servicekey=@servicekey
and endtime between @firsttime and @lasttime
and fee<>0
order by starttimeSelect * from @t_table where rownum >=@StartRow
GO
上面是一个分页的存储过程,运行之后提示没有定义变量“@tb”,但是我明明已经在开头定义了这个变量了。如果把参数◎tb直接用表名代替的话,就没有上面的错误。小弟刚刚开始写存储过程,语法方面不是很懂,请大家指教!
CREATE PROCEDURE paging@tb as varchar(10), --传入的表名称
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullASdeclare @t_table table
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount @StopRowexec('insert @t_table
(
callingnumber,callednumber,calltime,duration,callcost
)select callingnumber,truecallednumber,starttime,duration,feefrom '+@tb+ --提示没有定义变量'where callingnumber like @callingnumber and servicekey=@servicekey
and endtime between @firsttime and @lasttime
and fee<>0
order by starttime')Select * from @t_table where rownum >=@StartRow
GO
CREATE PROCEDURE paging@tb as varchar(10), --传入的表名称
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullASexec('declare @t_table table
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount @StopRowinsert @t_table
(
callingnumber,callednumber,calltime,duration,callcost
)select callingnumber,truecallednumber,starttime,duration,feefrom '+@tb+ --提示没有定义变量'where callingnumber like @callingnumber and servicekey=@servicekey
and endtime between @firsttime and @lasttime
and fee<>0
order by starttimeSelect * from @t_table where rownum >=@StartRow')
GO
select @chvSql='select a,b from '+@chvTableName
exec (@chvSql)
whbo(王红波(年轻人,要有所作为)) ,按你的办法,@chvTableName无法和我的@tb联系起来.
CREATE PROCEDURE paging@tb as varchar(10), --传入的表名称
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullASexec('
declare @t_table table
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount '+@StopRow+' insert into @t_table
(
callingnumber,callednumber,calltime,duration,callcost
)
select callingnumber,truecallednumber,starttime,duration,fee from '+@tb+' where callingnumber like '''+@callingnumber+''' and servicekey='''+@servicekey+''' and endtime between '''+@firsttime+''' and '''+@lasttime+''' and fee<>0
order by starttimeSelect * from @t_table where rownum >='+@StartRow)
GO
@callingnumber as varchar(20),
@servicekey as varchar(10),
@firsttime as varchar(20),
@lasttime as varchar(20),
@StartRow as int=null,
@StopRow as int=nullAScreate table ##
(
rownum int IDENTITY(1,1) Primary Key not null,
callingnumber varchar(20),
callednumber varchar(40),
calltime varchar(50),
duration varchar(10),
callcost varchar(20)
)
Set RowCount @StopRowexec('insert ##
(
callingnumber,callednumber,calltime,duration,callcost
)select callingnumber,truecallednumber,starttime,duration,feefrom '+@tb+' where callingnumber like '''+@callingnumber+''' and servicekey='''+@servicekey+'''
and endtime between '''+@firsttime+''' and '''+@lasttime+'''
and fee<>0
order by starttime')Select * from ## where rownum>=@StartRowdrop table ##
GO