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

原创 2005年05月05日 11:58:00

    开发中,我们经常用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

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

SQLSERVER存储过程不用游标循环临时表结果集

declare @deviceNos varchar(1000); declare @devicetemp table ( DEVICE_ID int, DE...
  • niechaoya
  • niechaoya
  • 2017年03月30日 15:41
  • 1659

不创建表结构,直接放入临时表结果(包括存储过程,直接sql语句)

首先介绍一下临时表的分类:临时表有两种    1,加#只有该会话可见,并且该会话结束后SQL   SERVER自动删除    2,加##全局。所有会话结束后SQL   SERVER   删除它  全局...
  • camel0564
  • camel0564
  • 2007年11月14日 10:40
  • 3527

sqlserver 存储过程 临时表 时间计算 循环 问题

今天要做统计网站访问量的功能。结果存储过程不会,各种系统函数也不会用。客户那边还没网络。着实憋气了。 下面是查到的一些东西 一 、临时表的使用 表名前使用一个#号,临时表是局部的,使用两个#号,临时...
  • luckysiesteven
  • luckysiesteven
  • 2015年02月02日 22:10
  • 1400

SQL SERVER 建临时表、循环插入数据、游标遍历数据库

SQL SERVER 建临时表、循环插入数据、游标遍历数据库
  • jyh_jack
  • jyh_jack
  • 2015年09月18日 18:28
  • 2563

多种临时表效率的对比分析

平时在写存储过程的时候,经常会用到临时表,而临时表的用法到底有几种,哪种效率更好呢?我们来做个对比分析。...
  • shuaihj
  • shuaihj
  • 2014年11月07日 11:01
  • 3212

sql server中的用户临时表和全局临时表的区别

临时表分为: 本地临时表,仅限于当前访问者访问,创建方法去如下: create table #TableName(表结构) 储存于数据库tempdb内(硬盘),当前用户断开连接,自动删除 如果...
  • fyifei0558
  • fyifei0558
  • 2012年08月06日 10:02
  • 1311

SQL临时表相关记录(WITH AS,#TMP)

一.WITH AS的含义      WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时...
  • zlhxiaohui
  • zlhxiaohui
  • 2014年10月11日 19:36
  • 587

SQLServer拆分字符串到临时表

GO Create function [dbo].[split] ( @SourceSql varchar(max), @StrSeprate varchar(10) ) returns ...
  • chinaboykai
  • chinaboykai
  • 2013年04月23日 09:47
  • 1695

sql server中如何创建,使用,释放临时表

drop table #Tmp   --删除临时表#Tmp create table #Tmp --创建临时表#Tmp (     ID   int IDENTITY (1,1)     not...
  • ArvinStudy
  • ArvinStudy
  • 2012年03月15日 17:30
  • 1810

sql server判断临时表是否存在

IF object_id(tempdb..#temp) is not null    print 存在!else    print 不存在! 
  • flynetcn
  • flynetcn
  • 2007年11月13日 17:44
  • 2258
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server(存储过程)临时表与滞后名称解析
举报原因:
原因补充:

(最多只允许输入30个字)