SQL Server STORED PROCEDURES CREATE PROCEDURE dbo Category

SQL Server STORED PROCEDURES

CREATE PROCEDURE dbo. Category. List WITH ENCRYPTION AS SELECT Product. Category. Name, Product. Category. Description FROM dbo. Product. Category; go EXEC sp_helptext ‘dbo. Category. List’; Result: The text for object ‘dbo. Category. List’ is encrypted.

ﺍﺟﺮﺍی یک ﺭﻭیﻪ ﺭﺍﻩ ﺩﻭﺭ EXEC [MAUINoliSQL 2 COPENHAGEN]. OBXKites. dbo. p. Product. Category_Add. New ‘Food’, ‘Eatables’; SELECT Customer. Type. Name, Discount. Percent, [Default] FROM OPENQUERY( [MAUICOPENHAGENNoliSQL 2], ‘EXEC OBXKites. dbo. p. Customer. Type_Fetch; ’);

USE OBXKites; go CREATE PROCEDURE dbo. Category. Get ( @Category. Name NVARCHAR(35) )AS SELECT Product. Category. Name, Product. Category. Description FROM dbo. Product. Category WHERE Product. Category. Name = @Category. Name; Go EXEC dbo. Category. Get N’Kite’;

CREATE PROCEDURE dbo. p. Product. Category_Fetch 2 @Search NVARCHAR(50) = NULL -- If @Search = null then return all Product. Categories -- If @Search is value then try to find by Name AS SET NOCOUNT ON; SELECT Product. Category. Name, Product. Category. Description FROM dbo. Product. Category WHERE Product. Category. Name = @Search OR @Search IS NULL; IF @@Row. Count = 0 BEGIN; RAISERROR( ‘Product Category ‘’%s" Not Found. ’, 14, 1, @Search); END;

Create Procedure with Table-Valued Parameters CREATE TYPE Order. Details. Type AS Table ( Line. Number INT, Product. ID INT, Is. New BIT, Is. Dirty BIT, Is. Deleted BIT );

Create Procedure with Table-Valued Parameters CREATE PROC Order. Transaction. Update. TVP (@Order. ID INT OUTPUT, @Customer. ID INT, @Order. Date. Time, @Details as Order. Details. Type READONLY)AS SET No. Count ON ; Begin Try Begin Transaction; -- If @Order. ID is NULL then it’s a new order, so Insert Order If @Order. ID IS NULL BEGIN; Insert Orders(Order. Date, Customer. ID) Values (@Order. Date, @Customer. ID); SET @Order. ID = Scope_Identity(); END; SELECT * FROM @Details ; Commit Transaction; End Try Begin Catch; Roll. Back; End Catch RETURN;

ﻧﺤﻮﻩ ﻓﺮﺍﺧﻮﺍﻧی ﺭﻭیﻪ ﺑﺎ پﺎﺭﺍﻣﺘﺮ ﺍﺯ ﻧﻮﻉ ﺟﺪﻭﻝ Declare @Order. ID INT; DECLARE @Details. TVP as Order. Details. Type; INSERT @Details. TVP (Line. Number, Product. ID, Is. New, Is. Dirty, Is. Deleted) VALUES (5, 101, -1, 0), (2, 999, 0, -1, 0), (3, null, 0, 0, 0); EXEC Order. Transaction. Update. TVP @Order. ID = @Order. ID Output , @Customer. ID = ‘ 78’, @Order. Date = ‘ 2008/07/24’, @Details = @Details. TVP;

ﺑﺮگﺮﺩﺍﻧﺪﻥ ﻣﻘﺪﺍﺭ ﺍﺯ ﺭﻭیﻪ USE OBXKites; go CREATE PROC dbo. Get. Product. Name ( @Product. Code CHAR(10), @Product. Name VARCHAR(25) OUTPUT) AS SELECT @Product. Name = Product. Name FROM dbo. Product WHERE Code = @Product. Code; RETURN;

ﻧﺤﻮﻩ ﻓﺮﺍﺧﻮﺍﻧی ﺭﻭیﻪ ﺍﺧیﺮ USE OBXKites; DECLARE @Prod. Name VARCHAR(25); EXEC dbo. Get. Product. Name ‘ 1001’, @Prod. Name OUTPUT; PRINT @Prod. Name;

RETURN ﺍﺳﺘﻔﺎﺩﻩ ﺍﺯ CREATE PROC dbo. Is. It. OK ( @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 must be within 7 days of current date. ’;

sp_dropmessage <message number>