1 / 17

Stored Procedures

SQL Server. Stored Procedures. 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. اجرای یک رویه راه دور.

aine
Download Presentation

Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL Server StoredProcedures

  2. 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.

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

  4. 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’;

  5. 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;

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

  7. 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;

  8. نحوه فراخوانی رویه با پارامتر از نوع جدول • 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;

  9. برگرداندن مقدار از رویه • 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;

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

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

  12. 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>

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

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

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

  16. sp_dropmessage <message number>

More Related