h1

Crearea unei tranzactii in mssql

March 1, 2010

EXEMPLU TRANZACTIE (CONTINE MAI MULTE STATEMENT-URI SQL):

USE Northwind

GO

CREATE PROCEDURE dbo.AddSupplierProduct

@CompanyName nvarchar(40) = NULL

@ContactName nvarchar(40) = NULL

@ContactTitle nvarchar(40) = NULL

@Address nvarchar(60) = NULL

@City nvarchar(15) = NULL

@Region nvarchar(40) = NULL

@PostalCode nvarchar(10) = NULL

@Country nvarchar(15) = NULL

@Phone nvarchar(24) = NULL

@Fax nvarchar(24) =  NULL

@HomePage ntext = NULL

@ProductName nvarchar(40) = NULL

@CategoryID int = NULL

@QuantityPerUnit nvarchar(20) = NULL

@UnitPrice money = NULL

@UnitsInStock  smallint  = NULL

@UnitsOnOrder smallint = NULL

@ReorderLevel smallint = NULL

@Discontinued bit = NULL

In values se introduce valorile variabilelor

AS

BEGIN TRANSACTION

INSERT Suppliers(CompanyName,ContactName,Address,City,Region,PostalCode,Country,Phone)

VALUES(@CompanyName,@ContactName,@Address,@City,@Region,@PostalCode,@Country,@Phone)

IF @@error <>0

BEGIN

ROLLBACK TRAN

RETURN

END

DECLARE @InsertSupplierID int

SELECT @InsertSupplierID=@@identity

INSERT Products(ProductName,SupplierId,CategoryID,QuantityPerUnit,Discontinued)

VALUES

(@ProductName

@InsertSupplierID

@CategoryID,

@QuantityPerUnit

@Discontinued

IF @@error <>0

BEGIN

ROLLBACK TRAN

RETURN

END

COMMIT TRANSACTION –marcheaza sfarsitul unei tranzactii

ROLLBACK TRANSACTION –se foloseste in cazul in care apare o eroare in cadrul tranzactiei

PRINT – pur si simplu afiseaza un mesaj (fara a afisa eroarea cum face @@error)

Tabelele sistem se afla in master (sysmessages, sysindexes)

EXEMPLU RAISEERROR CU AFISARE MESAJ DE EROARE:

RAISEERROR(50010,16,1) WITH LOG

Exemplu complet handling errormessages:

EXEC sp_addmessage 50010,16,’CustomerID not found.’,@replace=’replace’

USE Northwind

GO

–Updates a customer phone number,Error checking ensure that a valid customer identification number is supplied

CREATE PROCEDURE UpdateCustomerPhone

@CustomerID nchar(5) = NULL

@Phone nvarchar(24) = NULL

AS

IF @CustomerID IS NULL

BEGIN

PRINT ‘ You must supply a valid CustomerID’

RETURN

END

/*Ensure a valid CustomerID is supplied*/

IF NOT EXISTS

(SELECT * FROM Customers WHERE CustomerID= @CustomerID)

BEGIN

RAISERROR(50010,16,1) – Customer not found

RETURN

END

BEGIN TRANSACTION

UPDATE Customers

SET Phone=@Phone

WHERE CustomerID = @CustomerID

/*Display message that the phone number for CompanyName has been updated

*/

SELECT ‘The phone number for ‘+@CustomerID+’has been updated to’+@Phone

COMMIT TRANSACTION  — marcheaza sfarsitul tranzactiei

GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: