Podstrony
- Strona startowa
- World Without Cancer The Story Of Vitamin b17 G Edward Griffin
- Richard A. Knaak WarCraft Dzien Smoka (2)
- Daniel Goleman Inteligencja emocjonalna
- Gibson William Mona Liza Turbo
- Clarke Arthur C Tajemnica Ramy (SCAN dal 1137)
- BURCKHARDT KULTURA ODRODZENIA WE WŁOSZECH wydrukowane wstęp i nota biograficzna
- Morrell Dav
- Silverberg Robert Zamkniety swiat (2)
- Gabriela Zapolska KaÂśka Kariatyda
- PoematBogaCzlowieka k.5z7
- zanotowane.pl
- doc.pisz.pl
- pdf.pisz.pl
- dacia.pev.pl
[ Pobierz całość w formacie PDF ]
. SET @custContact= Tobias Smythe SET @custTitle= Customer Representative SET @custAddress= 1000 Marina Village SET @custCity= Alameda SET @custRegion= CA SET @custPostalCode= 90900 SET @custCountry= USA SET @custPhone= (714) 3258233 SET @custFax= (714) 3258233 -- Call stored procedure to add new customerEXECUTE @retCode = AddCustomer @custID, @custName, @custContact, @custTitle,@custAddress, @custCity, @custRegion, @custPostalCode,@custCountry, @custPhone, @custFaxPRINT @retCodeThe AddACustomer batch will add a new customer only the first time it s executed.If you exe-cute it again without changing the customer s ID, the error code 2627 will be returned, along withthe following error message:Violation of PRIMARY KEY constraint PK_Customers.Cannot insert duplicate key inobject Customers.The statement has been terminated.You can either change the values passed to the stored procedure, or switch to the EnterpriseManager, open the Customers table, and delete the newly added line.Adding OrdersThe next example is substantially more complicated.This time we ll write a procedure to add a neworder.By its nature, this stored procedure must perform many tests, and it may abort the entire oper-ation at various stages of its execution.The AddOrder stored procedure must accept the customer sID, the employee s ID, the shipper s ID, the shipping address, and the order s details.If the specified chT38 Bonus Reference TRANSACT-SQLcustomer, employee, or shipper does not exist, the procedure must abort its execution and return anerror code to the caller.If any of these tests fail, then the stored procedure exits and returns theappropriate error code ( 100 if customer doesn t exit, 101 if employee doesn t exist and 102 ifshipper doesn t exist).If these tests don t fail, you can safely add the new order to the Orders table.The following oper-ations are implemented as a transaction.If one of them fails, then neither an order nor details will beadded to the corresponding tables.The stored procedure must add a new row to the Orders table,insert the current date in the OrderDate field, and then use the OrderID field s value to add theorder s lines in the Order Details table.The OrderID field is assigned a value automatically by SQLServer when a row is added to the Orders table.You can find out the ID of the new order by exam-ining the @@IDENTITY variable, which holds the value of the most recently added Identity valuefor the current connection.This value will be used to add detail rows in the Order Details table.Then the order s details are added to the Order Details table, one row at a time.Again, if one ofthem fails, the entire transaction will fail.The most common reason for failure is to submit a nonex-istent product ID.If you force your application s users to select product IDs from a list and validatethe quantity and discount for each product, then none of the operations will fail.The order details are passed to the AddOrder procedure as a long string, and this part deservessome explanation.Ideally, another stored procedure (or application) should be able to create a cursorand pass it as an argument to the AddOrder procedure.SQL Server s stored procedures can t acceptcursors as arguments, so another technique for passing an unknown number of arguments has to beused.In this example, I ve decided to store the ID, quantity, and discount of each product into astring variable.Each field has a fixed length in this string, so that it can be easily parsed.The productID is stored as an integer in the first six characters of the string, the quantity as another integer in thenext six characters, and the discount in the last six characters.Each order, therefore, takes up 18characters.If you divide the length of this string by 18, you ll get the number of detail lines.Then,you can call the SUBSTRING() function repeatedly to extract each detail s values and insert theminto the Order Details table.Once the product ID has been extracted from the string variable, you can use it to retrieve theproduct s price from the Products table.Here are T-SQL statements that retrieve the first product sprice and insert it along with the quantity and discount fields into the Order Details table:SET @ProdID = SUBSTRING(@Details, 1, 6)SET @Qty = SUBSTRING(@Details, 7, 6)SET @Dscnt = SUBSTRING(@Details, 13, 6)SELECT @Price=UnitPrice FROM Products WHERE ProductID=@ProdIDINSERT [Order Details] (OrderID, ProductID, Quantity, UnitPrice, Discount)VALUES (@OrderID, @ProdID, @Qty, @Price, @Dscnt)If a product with the specific ID doesn t exist in the Products table, the procedure doesn t takeany special action.The INSERT statement will fail to add the detail line because it will violate theCOLUMN FOREIGN KEY constraint FK_Order_Details_Products, and the procedure will rollback the transaction and return the error code 547.Listing 9 shows the complete code of the AddOrder stored procedure.Apart from syntacticaldifferences, it s equivalent to the VB code you would use to add an order to the database
[ Pobierz całość w formacie PDF ]