SQL Server(存储过程)临时表与滞后名称解析

    开发中，我们经常用Create Procedure命令创建存储过程，而在创建过程时实际发生的是，Query Analyzer检查其语法，检查完毕并正确后将其插入系统表syscomments中，而在过程中引用的对象名称在该过程被执行之前不被解析，这个技术叫做滞后名称解析。然而，这个技术却并不是和我们想象的一样，它也有鞭长莫及的地方。下面来看一个过程：

CREATE PROC testp @var int

AS

IF @var=1

    CREATE TABLE #mytemp(k1 int identity,c1 int)

ELSE

    CREATE TABLE #mytemp(k1 int identity,c1 varchar(2))

INSERT #mytemp DEFAULT VALUES

SELECT c1 FROM #mytemp

GO

当编译该过程时，出现以下错误：

也许你会问，@var不可能既等于1又不等于1，那问什麽会出现以上的错误呢？大家不要头晕，现在是在检查语法的时候，检查语法是逐行向下的，编译器不会管你的@var等不等于1，它只检查语法错误！可能你又要说：上面不是说对象名称滞后解析吗？临时表就是一个对象啊，而#mytemp是它的名称，既然是滞后解析，那就是忽略该名称不解析，怎麽可能会出现这种错误，别急，问题就出在这里！对比看一下下面的过程：

CREATE PROC testp2 @var int

AS

IF @var=1

    CREATE TABLE tempdb..mytemp(k1 int identity,c1 int)

ELSE

    CREATE TABLE tempdb..mytemp(k1 int identity,c1 varchar(2))

INSERT mytemp DEFAULT VALUES

SELECT c1 FROM mytemp

GO

奇怪的事情发生了，创建这个过程顺利地通过，这里只是把临时表替换成了一个永久性表，并没做其他任何改变，难道SQL Server在乎创建的是临时表还是永久性表，呵呵？发生的事情似乎是这样的，在将过程插入syscomments之前(存储过程编译后存放于系统表syscomments中)， SQL Server参照临时表解析CREATE TABLE，也许你会说将临时表换成一个table类型的变量就不会出现这样的问题，可是事实是不行，数据类型table同样受此局限。似乎是从SQL Server 7.0开始，支持永久性表的滞后名称解析，但不支持临时表的滞后名称解析。但不管是哪一种情况，第一个代码都无法执行，下面采取一个迂回策略来解决这个问题，请看：

CREATE PROC testp @var int

AS

CREATE TABLE #mytemp(k1 int identity)

IF @var=1

    ALTER TABLE #mytemp ADD c1 int

ELSE

    ALTER TABLE #mytemp ADD c1 varchar(2)

INSERT #mytemp DEFAULT VALUES

EXEC('SELECT c1 FROM #mytemp')

GO

在这里只创建表一次，然后修改它，注意｀EXEC('SELECT c1 FROM #mytemp')＇

这条语句是必要的，因爲新添加的列对于添加它的过程并非立即可见，如果去掉EXEC执行该过程时，将会显示这样的错误：

再説一次，新添加的列对于添加它的过程并非立即可见！然而以上的代码却带来了一个性能问题，因爲任何创建临时表并近一步处理它的存储过程都将导致该过程的执行计划重新编译，这对于高吞吐量环境中的大型过程而言，性能会大打折扣！以下过程将解决这个问题：

CREATE PROC test4

AS

INSERT #temp DEFAULT VALUES

SELECT c1 FROM #temp

GO

 

CREATE PROC test3

AS

CREATE TABLE #temp (k1 int identity,c1 varchar(2))

EXEC dbo.test4

GO

 

CREATE PROC test2

AS

CREATE TABLE #temp (k1 int identity,c1 int)

EXEC dbo.test4

GO

 

CREATE PROC test @var int

AS

IF @var=1

    EXEC dbo.test2

ELSE

    EXEC dbo.test3

GO

复杂性提高了，但是还是可以解决一些问题，另外，之所以在第二和第三个过程中冗余地调用第四个过程，是因爲临时表一旦超过其作用域就自动被删除！