请教SQL语句，谢！(100分)

运行时提示已存在#TempGoods<br>---------------------------------------------<br>IF @Type=0 then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INTO &nbsp;#TempGoods <br> &nbsp;FROM Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=0 AND Deleted=0 <br>ELSE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INTO &nbsp;#TempGoods <br> &nbsp;FROM Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=1 AND Deleted=0 <br>---------------------------------------------------------

create table #TempGoods(goodsID varchar(40),UserCode varchar(40),FullName varchar(40))<br><br><br>IF Type=0 &nbsp;<br>insert #TempGoods (goodsID,UserCode,FullName)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>&nbsp; &nbsp; &nbsp;FROM &nbsp;Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=0 AND Deleted=0 <br>ELSE<br>insert #TempGoods (goodsID,UserCode,FullName)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>&nbsp; &nbsp; &nbsp;FROM &nbsp;Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=1 AND Deleted=0

你通过SQL SERVER 的profiler跟综一下,看into 几次,如果两次肯定是不行了.<br><br>从你上面的代码看不出问题

IF Type=0 then<br>??<br>SQL有这样的？

表#TempGoods 已经存在，<br>在你的代码前加上<br>if object_id('tempdb..#TempGoods') is not null<br>&nbsp; DROP TABLE #TempGoods &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>---------------------------------------------<br>IF Type=0 then<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INTO &nbsp;#TempGoods <br> &nbsp;FROM Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=0 AND Deleted=0 <br>ELSE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT goodsID,UserCode,FullName<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INTO &nbsp;#TempGoods <br> &nbsp;FROM Goods<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE Sonnum=1 AND Deleted=0 <br>---------------------------------------------------------

感谢各位！<br>baobao2601的方法我采用过，但很奇怪的是出来的结果goodsID全部是同一个值，采用INTO #Tempgoods时却是正常的！<br>iKing的方法不行，我是在查询分析器运行，一点击运行就提示“数据库中已存在名为 '#Tempgoods' 的对象。”位置在第二个 '#Tempgoods' 上。

樓主,你錯了!<br><br>查詢分析器,在執行一次以下時,臨時表是有問題的.你測試運行時,要在最後面寫drop table #temptable<br><br>存儲過程中就不用加這句,不會錯的

因為查詢分析器記錄下了你上次運行的數據.沒有釋放掉.<br><br>所以.你要在運行第二次時刪除這個表.

楼上几位朋友都说对了<br><br>查询分析器是有这个问题的<br><br>就算是游标之类也是一样 <br><br>自己注意释放吧

谢各位！