|
3> SELECT top 5 sc.text
4> FROM syscomments sc
5> JOIN sysobjects so
6> ON sc.id = so.id
7>
8> GO
text
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Error Handling in SQL Server 2005
CREATE PROCEDURE spDeleteProduct @Productid int
AS
BEGIN TRY
BEGIN TRANSACTION
DELETE Product WHERE ProductID = @Productid
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @Err AS int
DECLARE @Msg AS varchar(max)
SET @Err = @@Error
SET @Msg = Error_Message()
ROLLBACK TRANSACTION
INSERT ErrorTable
VALUES (@err, @msg)
END CATCH
CREATE PROC dbo.usp_OrderDetailsInsert
@orderid int,
@partid int,
@quantity int
AS
INSERT INTO OrderDetails(orderid, partid, quantity)
VALUES(@orderid, @partid, @quantity)
CREATE PROC dbo.usp_OrderDetailsUpdate
@orderid int,
@partid int,
@neworderid int = NULL,
@newpartid int = NULL,
@quantity int = NULL
AS
UPDATE OrderDetails
SET orderid = ISNULL(@neworderid, orderid),
partid = ISNULL(@newpartid, partid),
quantity = ISNULL(@quantity, quantity)
WHERE
orderid = @orderid
AND
partid = @partid
CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
CREATE PROC dbo.usp_OrderDetailsDelete
@orderid int,
@partid int
AS
DELETE FROM OrderDetails
WHERE
orderid = @orderid
AND
partid = @partid
(5 rows affected)
|