一个SQL语句的迷惑(存储过程 定义游标)(20分)

  • 主题发起人 主题发起人 slqbs-gy
  • 开始时间 开始时间
S

slqbs-gy

Unregistered / Unconfirmed
GUEST, unregistred user!
我在SQL SERVER2000中写了这样一个SQL存储过程!<br>CREATE procedure memwinedetail<br> &nbsp; &nbsp;@d1 varchar(20),<br> &nbsp; &nbsp;@d2 varchar(20),<br> &nbsp; &nbsp;@mdcid varchar(20),<br> &nbsp; &nbsp;@cuid int<br>as<br>Create table #tmp(a1 varchar(20),a2 numeric,a3 numeric,a4 numeric,a5 varchar(50) Default('') )<br><br>Declare Cr_tmp cursor For <br> &nbsp;Select mdcid From memcard <br>Open Cr_tmp <br>Fetch Next From Cr_tmp into @mdcid <br>while (@@fetch_status = 0)<br>begin<br> &nbsp; select @cuid=isnull(max(cuid),0) from compartmentusedetail &nbsp;where Backchar2 like '%@mdcid%' and (cuddate between @d1 and @d2 ) and ifprint=1 &nbsp;<br> &nbsp;if @cuid!=0 begin<br> &nbsp; &nbsp; &nbsp;Insert into #tmp<br> &nbsp; &nbsp; &nbsp;select a.mdcid,a.othermoney3,a.othermoney4,a.jifen,'' from memcard as a where a.mdcid=@mdcid <br> &nbsp; &nbsp; &nbsp;union select a.clname,a.price,sum(a.num),sum(a.allprice),a.cudconsumekind from compartmentusedetail as a,memcard as b where b.mdcid=@mdcid &nbsp;and a.Backchar2 like '%@mdcid%' and (cuddate between @d1 and @d2 ) and ifprint=1 group by a.clid,a.clname,a.price,a.cudconsumekind <br> &nbsp; &nbsp; &nbsp;union select '消费总额' ,0,0,sum(allprice),'消费' from compartmentusedetail as a,memcard as b where b.mdcid=@mdcid &nbsp;and &nbsp;a.Backchar2 like '%@mdcid%' and cudconsumekind='消费'<br> &nbsp; &nbsp; end<br> &nbsp;Fetch Next From Cr_tmp into @mdcid<br>end<br>Close Cr_tmp<br>Deallocate Cr_tmp <br>GO <br>执行也是成功的,<br>当我在数据库中执行 exec memwinedetail '2000-10-10 8:00:00','2008-10-10 8:00:00','',0<br>提示也是成功的,按道理这时候数据库中应该有临时表 #tmp<br>可是当我执行 <br>select * from #tmp<br>却提示 #tmp 对象不存在!<br>很是不解,可能是因为哪个地方我做的不对,<br>请老鸟指教!
 
#tmp临时表出了存储过程就释放了,你直接再存储过程的最后 用select * from #tmp<br> 每次执行完毕存储过程就会返回这个表集
 
谢谢,<br>我来测试一下
 
已经释放了,你测试一下在tempdb数据库中可不可以查出来
 
xinjinren说得是<br>#tmp临时表出了存储过程就释放了,你直接再存储过程的最后 用select * from #tmp<br> 每次执行完毕存储过程就会返回这个表集
 
谢谢各位,<br>现在的问题是:where Backchar2 like '%@mdcid%' &nbsp;这个参数 @mdcid 好像变成字符‘@mdcid’而没有得到实际上的值,在这边where Backchar2 like '%@mdcid%',@mdcid应该怎么表示呢?
 
where Backchar2 like '%'+@mdcid+'%'
 
即统配符中,字符变量怎么表示?
 
多人接受答案了。
 
后退
顶部