15. SQLServer : 存储过程 创建存储过程{callout color="#eee1ce"}存储过程的名称是区分大小写的，执行时需要注意使用SQL Server Management Studio创建存储过程中，在查询菜单中“指定模板参数的值(Ctrl+Shift+M)”补充模板信息,编写过程中使用“分析(Ctrl+F5)”进行检查和分析{/callout}{alert type="info"}-- 语法 CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter_name [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] <procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: 天祈 -- Create date: 2022-11-12 -- Description: 测试存储过程 -- ============================================= CREATE PROCEDURE [dbo].[Test2] ( -- Add the parameters for the stored procedure here @a int = 0, @b nvarchar = '') AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @a, @b END GO{/alert}执行存储过程{alert type="info"}EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;{/alert}设置存储过程自执行{alert type="info"}打开SQL Server Management Studio,SQL Server代理–作业–点右键–新建作业定时自动执行SQL存储过程{/alert}指定参数{alert type="info"}USE AdventureWorks2012; GO -- Passing values as constants. EXEC dbo.uspGetWhereUsedProductID 819, '20050225'; GO -- Passing values as variables. DECLARE @ProductID int, @CheckDate datetime; SET @ProductID = 819; SET @CheckDate = '20050225'; EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate; GO -- Try to use a function as a parameter value. -- This produces an error message. EXEC dbo.uspGetWhereUsedProductID 819, GETDATE(); GO -- Passing the function value as a variable. DECLARE @CheckDate datetime; SET @CheckDate = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; USE AdventureWorks2012; GO IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetSalesYTD; GO CREATE PROCEDURE Sales.uspGetSalesYTD @SalesPerson nvarchar(50) = NULL -- NULL default value AS SET NOCOUNT ON; -- Validate the @SalesPerson parameter. IF @SalesPerson IS NULL BEGIN PRINT 'ERROR: You must specify the last name of the sales person.' RETURN END -- Get the sales for the specified sales person and -- assign it to the output parameter. SELECT SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN {/alert}参数{alert type="info"}输入参数允许调用方将数据值传递到存储过程或函数。输出参数允许存储过程将数据值或游标变量传递回调用方。 用户定义函数不能指定输出参数。每个存储过程向调用方返回一个整数返回代码。 如果存储过程没有显式设置返回代码的值，则返回代码为 0。{/alert}{alert type="info"}-- Create a procedure that takes one input parameter and returns one output parameter and a return code. CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Declare and initialize a variable to hold @@ERROR. DECLARE @ErrorSave INT SET @ErrorSave = 0 -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE EmployeeID = @EmployeeIDParm -- Save any nonzero @@ERROR value. IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; IF (@@ERROR <> 0) SET @ErrorSave = @@ERROR -- Returns 0 if neither SELECT statement had an error; otherwise, returns the last error. RETURN @ErrorSave{/alert}@@ERROR{alert type="info"}定义: 返回执行的上一个 Transact-SQL语句的错误号。注意事项:　* 如果前一个T-SQL语句没有执行错误，则返回0　* 如果前一个语句遇到错误，则返回错误号。如果错误是 sys.messages 目录视图中的错误之一，则 @@ERROR 将包含 sys.messages.message_id 列中表示该错误的值。可以在 sys.messages 中查看与 @@ERROR 错误号相关的文本信息{/alert}返回数据使用结果集返回数据的示例{alert type="info"}USE AdventureWorks2019; GO IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL DROP PROCEDURE Sales.uspGetEmployeeSalesYTD; GO CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD AS SET NOCOUNT ON; SELECT LastName, SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID; RETURN; GO {/alert}使用输出参数返回数据{alert type="info"}{/alert}