存储过程变量空间问题

显示全部楼层 · 2005-7-16 18:49:11

在写存储过程的时候一直被一个问题困扰，那就是存储过程变量空间问题。例如，在存储过程中执行SQL语句有两种方法，一种是直接执行，例如“SELECT * FROM TABLE1”，另外一种是将SQL语句赋值到字符串变量，然后调用EXEC()函数。例如 SELECT @chvSQL = 'SELECT * FROM TABLE1' 然后执行 EXEC(@chvSQL) .
问题出在字符串中变量与存储过程变量不在同一空间中不能相互访问。例如，我想动态创建一个临时表，临时表的各列事先并不明确，而是动态生成的。我原来的作法是：
SELECT @chvSQL = 'SELECT COL1,COL2 INTO #MYTEMP FROM TABLE1'
SELECT * FROM #MYTEMP
但是由于变量空间不同的原因，在执行“SELECT * FROM #MYTEMP”时提示#MYTEMP对象不存在。我现在的解决方法是：
CREATE TABLE #MYTEMP(TEMPID INT)，然后动态增加临时表的列，感觉十分繁琐，出错的可能性也较大。所以请教各位，是否有更好的方法。
另外一个同样的问题是，SQL语句中的WHERE部分是动态生成的，保存在一个字符串变量中，导致只能以执行字符串的方式执行SQL语句。这样SQL语句的执行结果只能以临时表的方式传递，若是一个结果集，还好说。有的时候就是一个统计值也需要建立一个临时表传递，非常麻烦，不知各位高手有无更好的解决方法。
在这里多谢了。

千问 · 2005-7-16 18:49:11

是的﹐這個問題一般我是這樣處理的。
declare @sql varchar(7999)
select @sql='declare @a int,@b int select @a=2,@b=3 select @a,@b'
exec(@sql)
這種方案就是全部動態處理。
另一種和你差不多﹐使用臨時表
create table #tmp(a int,b int)
declare @sql varchar(7999)
select @sql='select 1,2'
insert into #tmp exec(@sql)
select * from #tmp
drop table #tmp

千问 · 2005-7-16 18:49:11

我不知道你所说的同一空间是什么概念，在sqlserver中据我所知只能定义局部变量，象临时表和@都是，在前台开发工具里倒可以定义全局变量。
对于动态处理，除了xzh2000兄所说的exec()，insert...exec生成临时表外，还有动态游标和sp_executesql可以选择。实际上在procedure中还常套用procedure来进行一些复杂的处理。

千问 · 2005-7-16 18:49:11

感谢两位斑竹的精彩回答，没有想到我的问题这么快就有回复，正是出乎我的意料，希望ITPUB越办越好！
所谓同一空间指的是变量空间。变量所位于的变量空间不同，就无法相互访问。
在SQL里，字符串的变量空间与其所存在的存储过程空间是不同的。
例如，我用EXEC('CREATE TABLE#mytemp(COLA INT,COLB INT)')创建一个临时表，是无法在存储过程中使用的。即，若我用SELECT * FROM # mytemp 会提示# mytemp对象不存在。
同样的问题，为了减少逐列判断类型的麻烦，我想用SELECT...INTO语句动态的生成临时表。若用EXEC()方法，就存在我所说的字符串空间与存储过程空间不一致，用sp_executesql好像是个办法，等下我回去试试。

千问 · 2005-7-16 18:49:11

我明白你意思了。