Stored Procedures

SQL Server StoredProcedures

CREATE PROCEDURE dbo.CategoryList • WITH ENCRYPTION • AS • SELECT ProductCategoryName, ProductCategoryDescription • FROM dbo.ProductCategory; • go • EXEC sp_helptext ‘dbo.CategoryList’; • Result: • The text for object ‘dbo.CategoryList’ is encrypted.

اجرای یک رویه راه دور • EXEC [MAUINoli\SQL2COPENHAGEN].OBXKites.dbo.pProductCategory_AddNew ‘Food’, ‘Eatables’; • SELECT CustomerTypeName, DiscountPercent, [Default] • FROM OPENQUERY( • [MAUI\COPENHAGENNoli\SQL2], ‘EXEC OBXKites.dbo.pCustomerType_Fetch;’);

USE OBXKites; • go • CREATE PROCEDURE dbo.CategoryGet ( • @CategoryName NVARCHAR(35))AS • SELECT ProductCategoryName, ProductCategoryDescription • FROM dbo.ProductCategory • WHERE ProductCategoryName = @CategoryName; • Go • EXEC dbo.CategoryGet N’Kite’;

CREATE PROCEDURE dbo.pProductCategory_Fetch2 • @Search NVARCHAR(50) = NULL • -- If @Search = null then return all ProductCategories • -- If @Search is value then try to find by Name • AS • SET NOCOUNT ON; • SELECT ProductCategoryName, ProductCategoryDescription • FROM dbo.ProductCategory • WHERE ProductCategoryName = @Search • OR @Search IS NULL; • IF @@RowCount = 0 • BEGIN; • RAISERROR( • ‘Product Category ‘’%s" Not Found.’,14,1,@Search); • END;

Create Procedure with Table-Valued Parameters • CREATE TYPE OrderDetailsType AS Table ( • LineNumber INT, • ProductID INT, • IsNew BIT, • IsDirty BIT, • IsDeleted BIT • );

Create Procedure with Table-Valued Parameters • CREATE PROC OrderTransactionUpdateTVP (@OrderID INT OUTPUT, • @CustomerID INT, • @OrderDate DateTime,@Details as OrderDetailsType READONLY)AS • SET NoCount ON ; • Begin Try • Begin Transaction; • -- If @OrderID is NULL then it’s a new order, so Insert Order • If @OrderID IS NULL • BEGIN; • Insert Orders(OrderDate, CustomerID) Values (@OrderDate, @CustomerID); • SET @OrderID = Scope_Identity(); • END; • SELECT * FROM @Details ; • Commit Transaction; • End Try • Begin Catch; • RollBack; • End Catch • RETURN;

نحوه فراخوانی رویه با پارامتر از نوع جدول • Declare @OrderID INT; • DECLARE @DetailsTVP as OrderDetailsType; • INSERT @DetailsTVP (LineNumber,ProductID,IsNew,IsDirty,IsDeleted) • VALUES(5, 101, -1, -1, 0), • (2, 999, 0, -1, 0), • (3, null, 0, 0, 0); • EXEC OrderTransactionUpdateTVP • @OrderID = @OrderID Output , • @CustomerID = ‘78’, • @OrderDate = ‘2008/07/24’, • @Details = @DetailsTVP;

برگرداندن مقدار از رویه • USE OBXKites; • go • CREATE PROC dbo.GetProductName ( • @ProductCode CHAR(10), • @ProductName VARCHAR(25) OUTPUT) • AS • SELECT @ProductName = ProductName • FROM dbo.Product • WHERE Code = @ProductCode; • RETURN;

نحوه فراخوانی رویه اخیر • USE OBXKites; • DECLARE @ProdName VARCHAR(25); • EXEC dbo.GetProductName ‘1001’, @ProdName OUTPUT; • PRINT @ProdName;

RETURNاستفاده از • CREATE PROC dbo.IsItOK ( • @OK VARCHAR(10))AS • IF @OK = ‘OK’ • BEGIN; • RETURN 0; • END; • ELSE • BEGIN; • RETURN -100; • END;

Procedureشکل کلی دستور ایجاد • CREATE PROCEDURE|PROC <name> • [<parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT [PUT]] [READONLY] [, ……]] • [WITH RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|<’user name’>}] • AS <code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

RAISERROR (<message ID | message string | variable>, <severity>, <state> • [, <argument> • [,<...n>]] ) • [WITH LOG|SETERROR|NOWAIT]

sp_addmessage [@msgnum =] <msg id>, • [@severity =] <severity>, • [@msgtext =] <’msg‘> • [, [@lang =] <’language‘>] • [, [@with_log =] [TRUE|FALSE]] • [, [@replace =] ‘replace‘]

sp_addmessage • @msgnum = 60000, • @severity = 10, • @msgtext = ‘%s is not a valid Order date. • Order date must be within 7 days of current date.’;

sp_dropmessage <message number>

Stored Procedures