首页 / MSSQL / sqlserver存储过程语法详解

sqlserver存储过程语法详解

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了sqlserver存储过程语法详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6210字，纯文字阅读大概需要9分钟。

内容图文

存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。
定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。这样做的好处至少有三个：
第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。
第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。
第三、有利于SQL语句的重用。

在ASP中，一般通过command对象调用存储过程，根据不同情况，本文也介绍其它调用方法。为了方便说明，根据存储过程的输入输出，作以下简单分类：
1. 只返回单一记录集的存储过程
假设有以下存储过程(本文的目的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)：

代码如下:

/*SP1*/
    CREATE PROCEDURE dbo.getUserList
    as
    set nocount on
    begin
       select * from dbo.[userinfo]
    end
    go

以上存储过程取得userinfo表中的所有记录，返回一个记录集。通过command对象调用该存储过程的ASP代码如下:

代码如下:

'**通过Command对象调用存储过程**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr  'MyConStr是数据库连接字串
MyComm.CommandText  = "getUserList" '指定存储过程名
MyComm.CommandType  = 4 '表明这是一个存储过程
MyComm.Prepared = true  '要求将SQL命令先行编译
Set MyRst = MyComm.Execute
Set MyComm = Nothing

存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。
在以上代码中，CommandType属性表明请求的类型，取值及说明如下：
-1 表明CommandText参数的类型无法确定

1表明CommandText是一般的命令类型
2表明CommandText参数是一个存在的表名称
4表明CommandText参数是一个存储过程的名称

还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：

代码如下:

'**通过Connection对象调用存储过程**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open MyConStr'MyConStr是数据库连接字串
Set MyRst = MyConn.Execute("getUserList",0,4) '最后一个参断含义同CommandType
Set MyConn = Nothing

'**通过Recordset对象调用存储过程**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1,4
'MyConStr是数据库连接字串,最后一个参断含义与CommandType相同

2. 没有输入输出的存储过程
请看以下存储过程：

代码如下:

/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go

该存储过程删去userinfo表中的所有记录，没有任何输入及输出，调用方法与上面讲过的基本相同，只是不用取得记录集：

代码如下:

'**通过Command对象调用存储过程**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
MyComm.CommandText = "delUserAll" '指定存储过程名
MyComm.CommandType = 4 '表明这是一个存储过程
MyComm.Prepared = true '要求将SQL命令先行编译
MyComm.Execute '此处不必再取得记录集

Set MyComm = Nothing

当然也可通过Connection对象或Recordset对象调用此类存储过程，不过建立Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利用Command对象吧。

3. 有返回值的存储过程
在进行类似SP2的操作时，应充分利用SQL Server强大的事务处理功能，以维护数据的一致性。并且，我们可能需要存储过程返回执行情况，为此，将SP2修改如下：

代码如下:

/*SP3*/
    CREATE PROCEDURE dbo.delUserAll
    as
    set nocount on
    begin
       BEGIN TRANSACTION
       delete from dbo.[userinfo]
       IF @@error=0
          begin
             COMMIT TRANSACTION
             return 1
          end
       ELSE
          begin
             ROLLBACK TRANSACTION
             return 0
          end        
       return
    end
    go

以上存储过程，在delete顺利执行时，返回1，否则返回0，并进行回滚操作。为了在ASP中取得返回值，需要利用Parameters集合来声明参数：

代码如下:

'**调用带有返回值的存储过程并取得返回值**
    DIM MyComm,MyPara
    Set MyComm = Server.CreateObject("ADODB.Command")
    MyComm.ActiveConnection = MyConStr          'MyConStr是数据库连接字串
    MyComm.CommandText      = "delUserAll"      '指定存储过程名
    MyComm.CommandType      = 4                 '表明这是一个存储过程
    MyComm.Prepared         = true              '要求将SQL命令先行编译
    '声明返回值
    Set Mypara = MyComm.CreateParameter("RETURN",2,4)

    MyComm.Parameters.Append MyPara
    MyComm.Execute
    '取得返回值
    DIM retValue
    retValue = MyComm(0)    '或retValue = MyComm.Parameters(0)
    Set MyComm = Nothing

在MyComm.CreateParameter("RETURN",2,4)中，各参数的含义如下：
第一个参数("RETURE")为参数名。参数名可以任意设定，但一般应与存储过程中声明的参数名相同。此处是返回值，我习惯上设为"RETURE"；
第二个参数(2)，表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常用的类型代码：

代码如下:

adBigInt: 20 ;
    adBinary : 128 ;
    adBoolean: 11 ;
    adChar: 129 ;
    adDBTimeStamp: 135 ;
    adEmpty: 0 ;
    adInteger: 3 ;
    adSmallInt: 2 ;
    adTinyInt: 16 ;
    adVarChar: 200 ;

对于返回值，只能取整形，且-1到-99为保留值；
第三个参数(4)，表明参数的性质，此处4表明这是一个返回值。此参数取值的说明如下：
0 : 类型无法确定； 1: 输入参数；2: 输入参数；3：输入或输出参数；4: 返回值

以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实

代码如下:

Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara 

可以简化为

代码如下:

MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4)

甚至还可以继续简化，稍后会做说明。
对于带参数的存储过程，只能使用Command对象调用(也有资料说可通过Connection对象或Recordset对象调用，但我没有试成过)。

4. 有输入参数和输出参数的存储过程
返回值其实是一种特殊的输出参数。在大多数情况下，我们用到的是同时有输入及输出参数的存储过程，比如我们想取得用户信息表中，某ID用户的用户名，这时候，有一个输入参数----用户ID，和一个输出参数----用户名。实现这一功能的存储过程如下：

代码如下:

/*SP4*/
    CREATE PROCEDURE dbo.getUserName
       @UserID int,
       @UserName varchar(40) output
    as
    set nocount on
    begin
       if @UserID is null return
       select @UserName=username
           from dbo.[userinfo]

您可能感兴趣的文章:

SQLServer存储过程中事务的使用方法
谈谈sqlserver自定义函数与存储过程的区别
SQLServer用存储过程实现插入更新数据示例
使用sqlserver存储过程sp_send_dbmail发送邮件配置方法(图文)
sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享
sqlserver2008查看表记录或者修改存储过程出现目录名无效错误解决方法
sqlserver 存储过程中If Else的用法实例
SqlServer获取存储过程返回值的实例
sqlserver 各种判断是否存在(表名、函数、存储过程等)
SQL Server 存储过程遇到“表 ''''#TT'''' 没有标识属性无法执行 SET 操作”错误

内容总结

以上是互联网集市为您收集整理的sqlserver存储过程语法详解全部内容，希望文章能够帮你解决sqlserver存储过程语法详解所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/586528.html

来源：【匿名】

【上一篇】SQLServer页类型汇总+疑问汇总【下一篇】一个odbc连mssql分页的类

更多 ►

【sqlserver存储过程语法详解】教程文章相关的互联网学习教程文章

MSSQL存储过程实现拼接sql的注意点【代码】

这里我昨天碰到的问题就是执行一段根据变量tableName对不同的表进行字段状态的更改。由于服务器原因，我不能直接在数据访问层写SQL，所以只好抽离出来放到存储过程里面。这里就出现了一个问题，我花费了好久才弄通！　　其实就是很简单的一个SQL语句：　　update table1 set field1=value1,field2 = value2 where id = id　　我写成什么样子了呢？大家且看：　declare@tableNamenvarchar(50),@field1int,@field2nvarchar,@idintdec...

MSSQL手札三 MSSQL存储过程【代码】

--存储过程完成一段sql代码的封装createproc trim --参数列表，多个间用逗号分隔@strvarchar(10) as--自定义代码段declare@str1varchar(10) set@str1=LTRIM(RTRIM(@str)) print@str1--使用存储过程exec trim ‘ abc ‘--‘abc‘ --===带输出参数的存储过程 --求两个数的和createproc sum1 @num1int, @num2int, @resultint output--表示这个参数可以将结果带出存储过程asset@result=@num1+@num2declare@r1intexec sum1 1,2,@r1 outp...

MSSQL存储过程1【代码】

ALTER PROCEDURE SP_TEST ASBEGINif 1=0SELECT UserName,TrainerID,TrainerInfoID FROM T_TrainerInfo;elseselect * from T_UserInfo; ENDGO原文：http://my.oschina.net/0x4ad/blog/413895

SQL Server 存储过程【代码】

Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。? 存储过程的概念存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返...

sql server存储过程简单使用【代码】【图】

1 创建存储过程　　1.1 创建简单的存储过程　　　创建语句：createproc usp_helloworld asbeginprint‘Hello World‘end　　创建完后，调用语句格式 exec + 存储过程名：exec usp_helloworld执行结果：　　1.2 创建带参数的存储过程　　　语句：createproc usp_book @namenvarchar(50), @authornvarchar(50) asbeginselect*from book where name=@nameand author=@authorend　　表中的数据：　　调用存储过程，因为定义两个参数，在...

SqlServer中存储过程 returnC#代码处理以及对应的MySQL如何改写【代码】

一.SqlServer 中 1. 创建表 createtable testuser(id int, --primary key,names varchar(50),address varchar(50),paw varchar(50) )2.创建存储过程createproc testp12(@idint ,@namesvarchar(50),@addressvarchar(50),@pawvarchar(50)) asbegindeclare@maxidintif@idisnullor@id<5beginreturn-1;end--set IDENTITY_INSERT testuser oninsertinto testuser(id,names,address,paw)values(@id,@names,@address,@paw) --set IDENTITY_...

SQL server 变量if，while，存储过程【图】

一.变量1.if循环 2.3.while循环declare @ss int 　　set @ss =2while @ss<10begin 　　print ‘Hello‘ 　　set @ss=@ss+1end　　--break 跳出循环　　declare @sss int 　　set @sss=2　　while @sss<10begin 　　print‘Hello‘ 　　set @sss=@sss+1if @sss=6 　　breakend　　--continue 跳出本次循环，继续下次循环　　declare @ssss int 　　set @ssss=2while @ssss<10begin 　　set @ssss=@ssss+1 if @ssss>4an...

sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】

复制代码代码如下:--代码一DECLARE @cc INT SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1 SET @cc = @@ROWCOUNT SELECT n.* FROM news AS n WITH(NOLOCK), #tb As t WHERE t.RowIndex>@PageIndex*@PageSize AND t.RowIndex<=(@PageIndex+1)*@PageSize AND t.newsid=n.newsid SELECT @cc DROP TABLE #tb 复制代码代码如下:--...

SQL server 2008里面通过sys.dm_exec_procedure_stats得到存储过程的执行信息--转【代码】

--转自：http://blogs.msdn.com/b/apgcdsd/archive/2011/05/13/sql-server-2008-sys-dm-exec-procedure-stats.aspx很多DBA都希望能够得到某个存储过程的执行信息,比如: 1) 执行了多少次2) 执行的执行计划如何3) 执行的平均读写如何4) 执行平均需要多少时间等等. 幸运的是SQL server 2008 提供了一个这样的DMV，使得我们比较容易就得到上面的信息。这个DMV是sys.dm_exec_procedure_stats，它输出了下面的信...

SQL Server常用系统存储过程大全

1. sp_help 报告有关数据库对象（sys.sysobjects 兼容视图中列出的所有对象）　 sp_help 表名称,存储过程名称等2. sp_helpdb 报告有关数据库的有效信息 sp_helpdb 数据库名称3. sp_helptext 默认值,未加密的transact-SQL存储过程.用户定义Transaction-SQL函数.触发器.计算列,CHECK约束,视图活系统对象　　sp_helptext 存储过程名称等4 sp_helpindex 查看有关表或视图上的索引的信息　 sp_helpindex ...

.net SQLSERVER 分页存储过程

create PROCEDURE [dbo].[proc_SplitPage]@tblName varchar(255), -- 表名@strFields varchar(1000) = ‘*‘, -- 需要返回的列，默认*@strOrder varchar(255)=‘‘, -- 排序的字段名，必填@strOrderType varchar(10)=‘ASC‘, -- 排序的方式,默认ASC@PageSize int = 10, -- 页尺寸,默认10@PageIndex int = 1, -- 页码,默认1@strWhere varchar(1500) = ‘‘ -- 查询条件 (注意: 不要加 where)ASd...

一次性压缩Sqlserver2005中所有库日志的存储过程

通过下面这个可以执行单个数据库日志压缩，问题每次都要写数据库名字，日志大的话执行速度没有那么快有没有办法更快一点？有没有办法一次性收缩所有数据库？代码如下:alter database 数据库名 set recovery simple go dbcc shrinkdatabase (数据库名) go alter database 数据库名 set recovery full go 目前也有压缩日志的工具，一个B/S界面形式的操作压缩数据库的，就是在选择数据库的时候老需要重新去选择具体的库，而且数据...

压缩SQLServer数据库日志的一个存储过程

use master --注意,此存储过程要建在master数据库中 go if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_compdb]) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure [dbo].[p_compdb] GO create proc p_compdb @dbnause master --注意,此存储过程要建在master数据库中go if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[p_compdb]) and OBJECTPROPERTY(id, NIsProcedu...

一次性压缩Sqlserver2005中所有库日志的存储过程

有没有办法更快一点？有没有办法一次性收缩所有数据库？代码如下: alter database 数据库名 set recovery simple go dbcc shrinkdatabase (数据库名) go alter database 数据库名 set recovery full go 目前也有压缩日志的工具，一个B/S界面形式的操作压缩数据库的，就是在选择数据库的时候老需要重新去选择具体的库，而且数据库数量很大的时候，有些库被压缩了，并没有自动排序; 目前需要的是被压缩后的数据库自动滚到最后面，...

SQLSERVER临时表导致存储过程重编译(recompile)的一些探讨_MySQL【图】

SQLSERVER为了确保返回正确的值，或者处于性能上的顾虑，有意不重用缓存在内存里的执行计划，而重新编译执行计划的这种行为，被称为重编译（recompile）。那么引发存储过程重编译的条件有哪一些呢？下面罗列了一些导致重编译（recompile）的条件：- 对查询所引用的表或视图进行更改（ALTER TABLE 和 ALTER VIEW）。- 对执行计划所使用的任何索引进行更改。- 对执行计划所使用的统计信息进行更新，这些更新可能是从语句（如 UPDATE ...

首页 / MSSQL / sqlserver存储过程语法详解

sqlserver存储过程语法详解

内容导读

内容图文

您可能感兴趣的文章:

内容总结

内容备注

内容手机端

【sqlserver存储过程语法详解】教程文章相关的互联网学习教程文章

SQLSERVER - 相关标签

存储过程 - 相关标签

语法 - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程