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}

13. SQLServer : 常规命令 EXECUTE(简写EXEC)使用 EXECUTE 传递单个参数{alert type="info"}-- 执行存储过程 --EXEC dbo.uspGetEmployeeManagers @EmployeeID = 6; EXEC dbo.uspGetEmployeeManagers 6; -- 单个参数时不用声明 GO {/alert}使用多个参数{alert type="info"}DECLARE @CheckDate DATETIME; SET @CheckDate = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; {/alert}EXECUTE 'tsql_string'{alert type="info"}declare @str nvarchar(255) = 'select * from Production.Product' EXECUTE(@str) {/alert}对远程存储过程使用 EXECUTE 语句{alert type="info"}DECLARE @retstat INT; EXECUTE @retstat = SQLSERVER1.AdventureWorks2012.dbo.uspGetEmployeeManagers @BusinessEntityID = 6; {/alert}使用带存储过程变量的 EXECUTE 语句{alert type="info"}DECLARE @proc_name VARCHAR(30); SET @proc_name = 'sys.sp_who'; EXEC @proc_name; {/alert}使用带 DEFAULT 的 EXECUTE{alert type="info"}-- 创建存储过程 IF OBJECT_ID(N'dbo.ProcTestDefaults', N'P')IS NOT NULL DROP PROCEDURE dbo.ProcTestDefaults; GO -- Create the stored procedure. CREATE PROCEDURE dbo.ProcTestDefaults ( @p1 SMALLINT = 42, @p2 CHAR(1), @p3 VARCHAR(8) = 'CAR') AS SET NOCOUNT ON; SELECT @p1, @p2, @p3 ; GO -- 执行存储过程 -- Specifying a value only for one parameter (@p2). EXECUTE dbo.ProcTestDefaults @p2 = 'A'; -- Specifying a value for the first two parameters. EXECUTE dbo.ProcTestDefaults 68, 'B'; -- Specifying a value for all three parameters. EXECUTE dbo.ProcTestDefaults 68, 'C', 'House'; -- Using the DEFAULT keyword for the first parameter. EXECUTE dbo.ProcTestDefaults @p1 = DEFAULT, @p2 = 'D'; -- Specifying the parameters in an order different from the order defined in the procedure. EXECUTE dbo.ProcTestDefaults DEFAULT, @p3 = 'Local', @p2 = 'E'; -- Using the DEFAULT keyword for the first and third parameters. EXECUTE dbo.ProcTestDefaults DEFAULT, 'H', DEFAULT; EXECUTE dbo.ProcTestDefaults DEFAULT, 'I', @p3 = DEFAULT; {/alert}对用户定义函数使用 EXECUTE{alert type="info"}DECLARE @returnstatus NVARCHAR(15); SET @returnstatus = NULL; EXEC @returnstatus = dbo.ufnGetSalesOrderStatusText @Status = 2; PRINT @returnstatus; GO {/alert}使用 EXECUTE 重新定义单个结果集{alert type="info"}-- 对存储过程的返回结果重新定义列 EXEC uspGetEmployeeManagers 6 WITH RESULT SETS ( ([Reporting Level] INT NOT NULL, [ID of Employee] INT NOT NULL, [Employee First Name] NVARCHAR(50) NOT NULL, [Employee Last Name] NVARCHAR(50) NOT NULL, [Employee ID of Manager] NVARCHAR(max) NOT NULL, [Manager First Name] NVARCHAR(50) NOT NULL, [Manager Last Name] NVARCHAR(50) NOT NULL ) ); {/alert}使用 EXECUTE 重新定义两个结果集{alert type="info"}CREATE PROC Production.ProductList @ProdName NVARCHAR(50) AS -- First result set SELECT ProductID, Name, ListPrice FROM Production.Product WHERE Name LIKE @ProdName; -- Second result set SELECT Name, COUNT(S.ProductID) AS NumberOfOrders FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS S ON P.ProductID = S.ProductID WHERE Name LIKE @ProdName GROUP BY Name; GO -- Execute the procedure EXEC Production.ProductList '%tire%' WITH RESULT SETS ( (ProductID INT, -- first result set definition starts here Name NAME, ListPrice MONEY) , -- comma separates result set definitions (Name NAME, -- second result set definition starts here NumberOfOrders INT) ); {/alert}在运行时确定的名称调用存储过程{alert type="info"}EXEC ('EXEC ' + @var); {/alert}