Monday, August 22, 2005 6:42 PM
Let’s talk more about SQL Server 2005 new stuff! As before I will keep it short and sweet. I won't go into depth of what can be done with these commands because I think I would bored you, but I just want to give you a hint of what I've found cool.
The OUTPUT clause IMHO is a great time saver. The one thing I do with an insert stored procedure is to return the new key ID. Now, I haven’t been all that happy with using statements such as …
SELECT MAX(tableID) From DavesTable
… in the past working on the premise that the highest number in the key ID field is the latest, right? Now it’s not smart is it, because it may not be and there can be several reasons why it won’t ranging from the joys of a multi-user system to a manually entered field that has an key ID of say 1000!
So let me show you an example of how to use it. Lets say for instance you are entering new product names into a table.
CREATE PROC rpcProduct_Insert
@ProductName varchar(50)
AS
DECLARE @tblProduct TABLE (RecordID int, ProductName varchar(50))
INSERT INTO tblProduct (ProductName)
OUTPUT inserted.* INTO @tblProduct
VALUES (@ProductName)
SELECT * FROM @tblProduct
Exception Handling in previous SQL Server versions is pretty poor. Now T-SQL enjoys the new addition of TRY … CATCH methodology that is already enjoyed by .Net.
Let me give you an example,
CREATE PROC rpcTransferToSavings
@CustomerID int, @MoneyAmount money
AS
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
UPDATE SavingsAccount SET Balance = Balance + @MoneyAmount
WHERE CustomerID = @CustomerID
UPDATE CurrentAccount SET Balance = Balance - @MoneyAmount
WHERE CustomerID = @CustomerID
COMMIT TRAN
END TRY
BEGIN CATCH TRAN_ABORT
ROLLBACK TRAN
END CATCH