在SQL中存储过程的一般语法是什么？
1、 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
2、 创建不带参数存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--调用、执行存储过程
exec proc_get_student;
3、 修改存储过程
--修改存储过程
alter proc proc_get_student
as
select * from student;
4、 带参存储过程
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
5、 带通配符参数存储过程
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';
扩展资料：
SQL存储过程优点：
1、重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。
2、减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
3、安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。
参考资料来源：百度百科—存储过程
这里以创建名为 GetStuCou 的无参数存储过程为例：
create procedure GetStuCou
as
begin //开始存储过程
select * from Students left join Course c on s.C_S_Id=c.C_Id
end //结束存储过程
下面是存储过程的其他用法：
--创建存储过程
CREATE PROCEDURE PROC（后面接类型）
--定义变量--简单赋值
declare @a intset @a=5 print @a
--使用select语句赋值
declare @user1 nvarchar(50)
select @user1='张三'
print @user1
declare @user2 nvarchar(50)
--创建临时表1 create table #DU_User1
(
[ID] [int] NOT NULL，
[Oid] [int] NOT NULL，
)；
--定义一个游标
declare user_cur cursor for select ID,Oid,[Login] from ST_User
--打开游标
open user_cur
while @@fetch_status=0 begin
--读取游标
fetch next from user_cur into @ID,@Oid,@Login
print @ID
--print @Login
end
close user_cur
扩展资料：
创建存储过程的注意事项：
1、保持事务简短，事务越短，越不可能造成阻塞。
2、在事务中尽量避免使用循环while和游标，以及避免采用访问大量行的语句。
3、在启动事务前完成所有的计算和查询等操作，避免同一事务中交错读取和更新。可以使用表变量预先存储数据。即存储过程中查询与更新使用两个事务实现。
4、超时会让事务不执行回滚，超时后如果客户端关闭连接sqlserver自动回滚事务。如果不关闭，将造成数据丢失，而其他事务将在这个未关闭的连接上执行，造成资源锁定，甚至服务器停止响应。
5、避免超时后还可打开事务 SET XACT_ABORT ON统计信息可以优化查询速度,统计信息准确可以避免查询扫描，直接进行索引查找。
存储过程就是作为可执行对象存放在数据库中的一个或多个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]
参考资料： 开发者在线http://www.builder.com.cn/
一般分为十种情况，每种语法各不相同：
1、 创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
2、 创建不带参数存储过程
--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--调用、执行存储过程
exec proc_get_student;
3、 修改存储过程
--修改存储过程
alter proc proc_get_student
as
select * from student;
4、 带参存储过程
--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;
5、 带通配符参数存储过程
--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
6、 带输出参数存储过程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;7、 不缓存存储过程
--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;
8、 加密存储过程
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
9、 带游标参数存储过程
if (object_id('proc_cursor', 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标10、 分页存储过程
---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
存储过程（stored procedure）有时也称为sproc。存储过程存储于数据库中而不是在单独的文件中，有输入参数、输出参数以及返回值等。
在数据库中，创建存储过程和创建其他对象的过程一样，除了它使用的AS关键字外。存储过程的基本语法如下：
CREATE PROCDUER|PROC <sproc name>
[<parameter name>[schema.]<data type>[VARYING][=<default value>][OUT [PUT]][,
[<parameter name>[schema.]<data type>[VARYING][=<default value>][OUT [PUT]][,
...]]
[WITH
RECOMPILE|ENCRYPTION|[EXECUTE AS {CALLER|SELF|OWNER|<'user name'>}]
[FOR REPLICATION]
AS
<code>|EXTERNAL NAME <assembly name>.<assembly class>
存储过程示例一：
执行存储过程方法一：
|
广告 您可能关注的内容 |
