创建和执行存储过程

创建存储过程

CREATE PROCEDURE procedure_name

[WITH ENCRYPTION]

[WITH RECOMRILE]

AS

sql_statement

注：

WITH ENCRYPTION: 对存储过程进行加密

WITH RECOMPILE: 对存储过程重新编译

 

在book数据库中创建一个名为p_book1的存储过程。该存储过程返回book1表中所有出版社为“中国长安”的记录

USE book

GO

CREATE PROCEDURE p_book1

AS

SELECT * FROM book1 WHERE 出版社= ’中国长安‘

 

执行存储过程

EXEC procedure_name

 

-------------------------------------------------------------------------------------------------------------

 

带参数的存储过程

想存储过程设定输入、输出参数的主要目的是通过参数向存储过程输入和输出信息来扩展存储存储过程的功能。通过设定参数，可以多次使用同一存储过程并按用户要求查找所需要的结果。

带输入参数的存储过程

CREATE PROCEDURE procedure_name

@parameter_name datatype=[default]

[WITH ENCRYPTION]

[WITH RECOMPILE]

AS

sql_statement

注：

@parameter_name 存储过程的参数名，必须以@为前缀

Datatype 参数的数据类型

Default 参数的默认值，如果执行存储过程时为提供该参数的变量值，则使用default值。

 

CREATE PROCEDURE p_book1p

@出版社 varchar（20）

AS

SELECT * FROM book1

WHERE 出版社= @ 出版社

 

使用参数名传递参数值

EXEC Procedure_name

[@parameter_name = value]

[,...n]

例 用参数名传递参数值的方法执行存储过程p_book1p，分别查询出版社为“中国长安”和“安徽人民”书的记录

EXEC p_book1p @出版社='中国长安'

EXEC p_book1p @出版社='安徽人民'

 

按位置传递参数值

在执行存储过程的语句中，不通过参数传递参数值而直接给出参数的传递值。当存储过程含有多个输入参数时，传递值的顺序必须以存储过程中定义的输入顺序相一致。安位置传递参数时，也可以忽略空值和具有默认值的参数，但不能因此破坏输入参数的设定顺序。

EXEC parameter_name

[value1,value2,...]

 

带输出参数的存储过程

如果需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来是想，为了使用输出参数，需要在CREATE PROCEDURE 语句中指定OUTPUT关键字。

输出参数语法如下：

@parameter_name datatype = [default] OUTPUT

例：创建存储过程p_book1Num,要求能根据用户给定的出版社，统计出该出版社的出书数量，并将数量以输出变量的形式返回给用户。

CREATE PROCEDURE P_book1Num

@出版社 VARCHAR(20), @book1Num smallint output

AS

SET @book1Num =

(

SELECT COUNT(*) FROM book1

WHERE 出版社=@出版社

)

PRINT @book1Num

 

执行存储过程p_book1Num

DECLARE @出版社 VARCHAR(20), @book1Num SMALLINT

SET @出版社='中国长安'

EXEC p_book1Num @出版社，@book1Num