SQL Server存储过程Return、output参数及使用技巧

SQL Server目前正日益成为WindowNT操作系统上面最为重要的一种数据库管理系统,随着 SQL Server2000的推出,微软的这种数据库服务系统真正地实现了在WindowsNT/2000系列操作系统一统天下的局面,在微软的操作系统上,没有任何一种数据库系统能与之抗衡,包括数据库领域中的领头羊甲骨文公司的看家数据库Oracle在内。不可否认,SQL Server最大的缺陷就是只能运行在微软自己的操作系统上,这一点是SQL Server的致命点。但在另一方面却也成了最好的促进剂,促使SQL Server在自己仅有的“土地”上面将自己的功能发挥到了极至最大限度的利用了NT系列操作系统的各种潜能!作为SQL Server数据库系统中很重要的一个概念就是存储过程,合理的使用存储过程,可以有效的提高程序的性能;并且将商业逻辑封装在数据库系统中的存储过程中,可以大大提高整个软件系统的可维护性,当你的商业逻辑发生改变的时候,不再需要修改并编译客户端应用程序以及重新分发他们到为数众多的用户手中,你只需要修改位于服务器端的实现相应商业逻辑的存储过程即可。合理的编写自己需要的存储过程,可以最大限度的利用SQL Server的各种资源。下面我们来看看各种编写SQL Server存储过程和使用存储过程的技巧经验。 Input 此参数只用于将信息从应用程序传输到存储过程。 InputOutput 此参数可将信息从应用程序传输到存储过程,并将信息从存储过程传输回应用程序。 Output 此参数只用于将信息从存储过程传输回应用程序。 ReturnValue 此参数表示存储过程的返回值。SQL Server 的存储过程参数列表中不显示该参数。它只与存储过程的 RETURN 语句中的值相关联。   存储过程为主键生成新值后,通常使用存储过程中的 RETURN 语句返回该值,因此用来访问该值的参数类型是 ReturnValue 参数。 1、不带输入参数的简单存储过程 if object_id('up_user') is not null drop proc up_user go create proc up_user as set nocount on delcare @name varchar(10) begin select @name=uname from user end set nocount off go esec up_user 2、带输入参数的简单存储过程  if object_id('up_user') is not null drop proc up_user go create proc up_user @id int as set nocount on delcare @name varchar(10) begin select @name=uname from user where  uid=@id end set nocount off go --执行该存储过程 esec up_user 1 3、带Return参数的存储过程  if object_id('up_user') is not null drop proc up_user go create proc up_user as set nocount on delcare @age int begin select @age=uage from user return @age end set nocount off go --执行该存储过程 declare @age int exec @age=up_user select @age 4、带output参数的存储过程  if object_id('up_user') is not null drop proc up_user go create proc up_user @id int, @name varchar(10) =" output as set nocount on begin select @name=uname from user where uid=@id end set nocount off go --执行该存储过程 declare @name varchar(10) exec up_user 2, @name output select @name 5、同时带Return和output参数的存储过程 if exists(select name from sysobjects where name=’up_user' and type=’p') drop proc up_user go create proc up_user @id int, @name varchar(20) output as declare @age int begin select @age=stuage,@name=stuname from stuinfo where uid=@id return @age end --执行该存储过程 declare @age int declare @name varchar(20) exec @age=up_user 2,@name output select @age,@name 附:SQL Server 系统全局变量 @@CONNECTIONS 返回自上次启动以来连接或试图连接的次数。 @@CURSOR_ROWS 返回连接上最后打开的游标中当前存在的合格行的数量(返回被打开的游标中还未被读取的有效数据行的行数) @@DATEFIRST 返回每周第一天的数字 @@ERROR 返回最后执行的SQL 语句的错误代码。 @@FETCH_STATUS 返回被 FETCH 语句执行的最后游标的状态,而不是任何当前被连接打开的游标的状态。 @@IDENTITY 返回最后插入的标识值 @@LANGID 返回当前所使用语言的本地语言标识符(ID)。 @@LANGUAGE 返回当前使用的语言名。 @@LOCK_TIMEOUT 返回当前会话的当前锁超时设置,单位为毫秒。 @@PROCID 返回当前过程的存储过程标识符 (ID) 。 @@ROWCOUNT 返回受上一语句影响的行数。 @@SERVERNAME 返回运行 的本地服务器名称。 @@SPID 返回当前用户进程的服务器进程标识符 (ID)。 @@TRANCOUNT 返回当前连接的活动事务数。 @@VERSION 返回当前安装的日期、版本和处理器类型。 @@CPU_BUSY 返回自SQL Server 最近一次启动以来CPU 的工作时间其单位为毫秒 @@DATEFIRST 返回使用SET DATEFIRST 命令而被赋值的DATAFIRST 参数值SET DATEFIRST,命令用来指定每周的第一天是星期几 @@DBTS 返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的 @@ERROR 返回执行Transact-SQL 语句的错误代码 @@FETCH_STATUS 返回上一次FETCH 语句的状态值 @@IDLE 返回自SQL Server 最近一次启动以来CPU 处于空闭状态的时间长短单位为毫秒 @@IO_BUSY 返回自SQL Server 最近一次启动以来CPU 执行输入输出操作所花费的时间其单位为毫秒 @@LANGID 返回当前所使用的语言ID 值 @@LANGUAGE 返回当前使用的语言名称 @@LOCK_TIMEOUT 返回当前会话等待锁的时间长短其单位为毫秒 @@MAX_CONNECTIONS 返回允许连接到SQL Server 的最大连接数目 @@MAX_PRECISION 返回decimal 和numeric 数据类型的精确度 @@NESTLEVEL 返回当前执行的存储过程的嵌套级数初始值为0 @@OPTIONS 返回当前SET 选项的信息 @@PACK_RECEIVED 返回SQL Server 通过网络读取的输入包的数目 @@PACK_SENT 返回SQL Server 写给网络的输出包的数目 @@PACKET_ERRORS 返回网络包的错误数目 @@PROCID 返回当前存储过程的ID 值 @@REMSERVER 返回远程SQL Server 数据库服务器的名称 @@SERVICENAME 返回SQL Server 正运行于哪种服务状态之下如MSSQLServer MSDTC SQLServerAgent @@SPID 返回当前用户处理的服务器处理ID 值 @@TEXTSIZE 返回SET 语句的TEXTSIZE 选项值SET 语句定义了SELECT 语句中text 或image数据类型的最大长度基本单位为字节 @@TIMETICKS 返回每一时钟的微秒数 @@TOTAL_ERRORS 返回磁盘读写错误数目 @@TOTAL_READ 返回磁盘读操作的数目 @@TOTAL_WRITE 返回磁盘写操作的数目 @@TRANCOUNT 返回当前连接中处于激活状态的事务数目

ROW_NUMBER() OVER函数的基本用法用法

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) 简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。 示例: xlh           row_num 1700              1 1500              2 1085              3 710                4 row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的) 实例: 初始化数据 create table employee (empid int ,deptid int ,salary decimal(10,2)) insert into employee values(1,10,5500.00) insert into employee values(2,10,4500.00) insert into employee values(3,20,1900.00) insert into employee values(4,20,4800.00) insert into employee values(5,40,6500.00) insert into employee values(6,40,14500.00) insert into employee values(7,40,44500.00) insert into employee values(8,50,6500.00) insert into employee values(9,50,7500.00) 数据显示为 empid       deptid      salary ———-- ———-- ————————————— 1           10          5500.00 2           10          4500.00 3           20          1900.00 4           20          4800.00 5           40          6500.00 6           40          14500.00 7           40          44500.00 8           50          6500.00 9           50          7500.00 需求:根据部门分组,显示每个部门的工资等级 预期结果: empid       deptid      salary                                  rank ———-- ———-- ————————————— ——————-- 1           10          5500.00                                 1 2           10          4500.00                                 2 4           20          4800.00                                 1 3           20          1900.00                                 2 7           40          44500.00                               1 6           40          14500.00                               2 5           40          6500.00                                 3 9           50          7500.00                                 1 8           50          6500.00                                 2 SQL脚本: SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee   转自:http://www.cnblogs.com/digjim/archive/2006/09/20/509344.html 我们知道,SQL Server 2005和SQL Server 2000 相比较,SQL Server 2005有很多新特性。这篇文章我们要讨论其中的一个新函数Row_Number()。数据库管理员和开发者已经期待这个函数很久了,现在终于等到了! 通常,开发者和管理员在一个查询里,用临时表和列相关的子查询来计算产生行号。现在SQL Server 2005提供了一个函数,代替所有多余的代码来产生行号。 我们假设有一个资料库[EMPLOYEETEST],资料库中有一个表[EMPLOYEE],你可以用下面的脚本来产生资料库,表和对应的数据。 USE [MASTER] GO

IF  EXISTS SELECT * FROM SYS.OBJECTS HERE OBJECT_ID = OBJECT_ID(N'[DBO].[EMPLOYEE]') AND TYPE IN (N’U')) DROP TABLE [DBO].[EMPLOYEE] GO

我们可以用下面的脚本查询EMPLOYEE表。 SELECT EMPID, RNAME, LNAME FROM EMPLOYEE 这个查询的结果应该如图1.0 2021110 MICHAEL POLAND 2021110 MICHAEL POLAND 2021115 JIM KENNEDY 2121000 JAMES SMITH 2011111 ADAM ACKERMAN 3015670 MARTHA LEDERER 1021710 MARIAH MANDEZ 图1.0  在SQL Server 2005,要根据这个表中的数据产生行号,我通常使用下面的查询。 SELECT ROWID=IDENTITY(int,1,1) , EMPID, FNAME, LNAME INTO EMPLOYEE2 FROM EMPLOYEE ORDER BY EMPID 这个查询创建了一个新的表,用identify函数来产生行号。我们用下面的查询来看看这个表的数据。

上面的查询结果如图1.1 1 1021710 MARIAH MANDEZ 2 2011111 ADAM ACKERMAN 3 2021110 MICHAEL POLAND 4 2021110 MICHAEL POLAND 5 2021115 JIM KENNEDY 6 2121000 JAMES SMITH 7 3015670 MARTHA LEDERER 图1.1  这个查询结果很明显EMP=2021110的行是重复的数据。 要删除EMPID=2021110的重复数据,我们必须在EMPLOYEE2表中删除,不能直接在EMPLOYEE中删除。 SQL Server 2005提供了一个新的函数(Row_Number())来产生行号。我们可以使用这个新函数来删除原来表中的重复数据,只用通常的表达方式再加上Row_Number()函数。 让我们用Row_Number()函数根据EMPID来产生ROWID。 SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE 上面的查询结果如图1.2 1 1021710 MARIAH MANDEZ 2 2011111 ADAM ACKERMAN 3 2021110 MICHAEL POLAND 4 2021110 MICHAEL POLAND 5 2021115 JIM KENNEDY 6 2121000 JAMES SMITH 7 3015670 MARTHA LEDERER 图1.2  在这个结果中,我们可以区别EMPID是2021110的重复数据。 我们可以用通用表查询表达式和Row_Numner()函数来选出重复的那行数据。 WITH [EMPLOYEE ORDERED BY ROWID] AS (SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE) SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4 上面的查询结果如图1.3 4 2021110 MICHAEL POLAND 图1.3  这一行重复的数据可以用下面这个通用表和Row_Number()函数来删除。 WITH [EMPLOYEE ORDERED BY ROWID] AS (SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE) DELETE FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4 删除以后,我们可以用下面的查询语句看一下结果。 SELECT * FROM EMPLOYEE 这个查询结果如图1.4 2021110 MICHAEL POLAND 2021115 JIM KENNEDY 2121000 JAMES SMITH 2011111 ADAM ACKERMAN 3015670 MARTHA LEDERER 1021710 MARIAH MANDEZ 图 1.4  这里我们可以看到,重复的数据已经被删除了。 总结 在这篇文章中,我们讨论了SQL Server 2005 的新特性Row_Number()函数,还有通常的表表达式,然后如何使用这两个来删除重复的行。   转自:http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

存储过程中执行动态Sql语句

MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有。还有一个最大的好处就是利用sp_executesql,能够重用执行计划,这就大大提供了执行性能,还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非您有令人信服的理由使用EXEC,否侧尽量使用sp_executesql. 1.EXEC的使用 EXEC命令有两种用法,一种是执行一个存储过程,另一种是执行一个动态的批处理。以下所讲的都是第二种用法。 下面先使用EXEC演示一个例子,代码1   代码 DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql =      ’SELECT * FROM '+QUOTENAME(@TableName) +’WHERE OrderID = '+      CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC' EXEC(@sql);   注:这里的EXEC括号中只允许包含一个字符串变量,但是可以串联多个变量,如果我们这样写EXEC: EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+            QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');   SQL编译器就会报错,编译不通过,而如果我们这样:

  编译器就会通过; 所以最佳的做法是把代码构造到一个变量中,然后再把该变量作为EXEC命令的输入参数,这样就不会受限制了。 EXEC的缺点是不提供接口,这里的接口是指,它不能执行一个包含一个带变量符的批处理,如下 代码 DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +      ’WHERE OrderID = @OrderID ORDER BY ORDERID DESC' EXEC(@sql); 关键就在SET @sql这一句话中,如果我们运行这个批处理,编译器就会产生一下错误 Msg 137, Level 15, State 2, Line 1 必须声明标量变量 "@OrderID"。 使用EXEC时,如果您想访问变量,必须把变量内容串联到动态构建的代码字符串中,如: SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +    ’WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'   串联变量的内容也存在性能方面的弊端。SQL Server为每一个的查询字符串创建新的执行计划,即使查询模式相同也是这样。为演示这一点,先清空缓存中的执行计划 DBCC FREEPROCCACHE (这个不是本文所涉及的内容,您可以查看MS的MSDN) 将代码1运行3次,分别对@OrderID 赋予下面3个值,10251,10252,10253。然后使用下面的代码查询  

  点击F5运行,我们可以看到,每执行一次都要产生一次的编译,执行计划没有得到充分重用。 EXEC除了不支持动态批处理中的输入参数外,他也不支持输出参数。默认情况下,EXEC把查询的输出返回给调用者。例如下面代码返回Orders表中所有的记录数  

然而,如果你要把输出返回给调用批处理中的变量,事情就没有那么简单了。为此,你必须使用INSERT EXEC语法把输出插入到一个目标表中,然后从这表中获取值后赋给该变量,就像这样: 代码

  2.sp_executesql的使用 sp_executesql命令在SQL Server中引入的比EXEC命令晚一些,它主要为重用执行计划提供更好的支持。 为了和EXEC作一个鲜明的对比,我们看看如果用代码1的代码,把EXEC换成sp_executesql,看看是否得到我们所期望的结果   代码

    注意最后一行;事实证明可以运行; sp_executesql提供接口 sp_executesql命令比EXEC命令更灵活,因为它提供一个接口,该接口及支持输入参数也支持输出参数。这功能使你可以创建带参数的查询字符串,这样就可以比EXEC更好的重用执行计划,sp_executesql的构成与存储过程非常相似,不同之处在于你是动态构建代码。它的构成包括:代码快,参数声明部分,参数赋值部分。说了这么多,还是看看它的语法:  

@stmt参数是输入的动态批处理,它可以引入输入参数或输出参数,和存储过程的主体语句一样,只不过它是动态的,而存储过程是静态的,不过你也可以在存储过程中使用sp_executesql; @params参数与定义输入/输出参数的存储过程头类似,实际上和存储过程头的语法完全一样; @<params assignment> 与调用存储过程的EXEC部分类似。 其实@stmt,@params可以省略,那么exec sp_executesql的语法就可以简写成如下格式:

  为了说明sp_executesql对执行计划的管理优于EXEC,我将使用前面讨论EXEC时用到的代码。   代码

  下面我们看看exec sp_executesql的执行效率,在调用该代码和检查它生成的执行计划前,先清空缓存中的执行计划; DBCC FREEPROCCACHE 将上面的动态代码执行3次,每次执行都赋予@OrderID 不同的值,然后查询sys.syscacheobjects表,并注意它的输出,优化器只创建了一个备用计划,而且该计划被重用的3次   SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects   WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%' 点击F5运行。 sq_executesql的另一个与其接口有关的强大功能是,你可以使用输出参数为调用批处理中的变量返回值。利用该功能可以避免用临时表返回数据,从而得到更高效的代码和更少的重新编译。定义和使用输出参数的语法与存储过程类似。也就是说,你需要在声明参数时指定OUTPUT子句。例如,下面的静态代码简单的演示了如何从动态批处理中利用输出参数@p把值返回到外部批处理中的变量@i.  

  以字母 N 为前缀标识 Unicode 字符串常量  总结以下几点:   一.使用exce sp_executesql效率比exec要高,同一类型的语句,只需编译一次即可,而exec执行几次就需要编译几次。 二.构造动态sql的where子句,也就是条件子句时,exec无法使用变量来进行站位,需要将变量转换成字符串,然后和动态sql进行拼接,这就可能引起Sql注入问题,如下: SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +    ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'   而若使用exec sp_executesql则可以使用变量来进行站位,以后再给这个参数传值的放式构造动态sql,就避免的Sql注入的问题,如下:

三.无论是Exec还是Exec sp_executesql,如果想要将表名和列名进行动态参数化,不可以使用表名参数和列名参数来进行站位,而且表名参数和列名参数需要使用存储过程的参数.对     于exec sp_executesql来说,不可以将表名参数和列名参数在指定为在exec sp_executesql参数声明部分声明的参数,如: 代码

也就是说exec sp_executesql语句的参数声明部分只能声明动态sql的where子句的参数。   转自:http://www.cnblogs.com/RascallySnake/archive/2010/05/20/1739839.html

SQL Server 存储过程

Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。   1、 存储过程的优点 A、 存储过程允许标准组件式编程 存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。 B、 存储过程能够实现较快的执行速度 如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。 C、 存储过程减轻网络流量 对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。 D、 存储过程可被作为一种安全机制来充分利用 系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。   Ø 系统存储过程 系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。 常用系统存储过程有:

系统存储过程示例:

  Ø 用户自定义存储过程 1、 创建语法

  2、 创建不带参数存储过程

3、 修改存储过程

4、 带参存储过程

5、 带通配符参数存储过程

6、 带输出参数存储过程

7、 不缓存存储过程

8、 加密存储过程

9、 带游标参数存储过程

10、 分页存储过程

Ø Raiserror Raiserror返回用户定义的错误信息,可以指定严重级别,设置系统变量记录所发生的错误。 语法如下:

# msg_id:在sysmessages系统表中指定的用户定义错误信息 # msg_str:用户定义的信息,信息最大长度在2047个字符。 # severity:用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时,raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。 任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。 # state:介于1至127直接的任何整数。State默认值是1。