Mega Code Archive

 

Categories / MSSQL / System

 

Join syscomments and sysobjects

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)