Geeks With Blogs

News Dave's Mug View David Oliver's profile on LinkedIn Add to Technorati Favorites Blog Directory for Guildford, Surrey
Dave Oliver's Blog Enterprise Technology Thought Leadership in a FTSE 100

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

Posted on Monday, August 22, 2005 6:42 PM | Back to top


Comments on this post: More new SQL Server 2005 commands: Part 2

# re: More new SQL Server 2005 commands: Part 2
Requesting Gravatar...
Just a note on key IDs. I usually create the ID field as an integer type with Identity so it automatically assigns the next number in the series. This avoids cumbersome Max (column) + 1 syntax. To retrieve the new identity value for the row I just inserted I use the @@Identity function.

I typically combine the insert stored proc with update by including the row id as a parameter. When it's zero then you're inserting, when it's greater than zero you're updating.

Example based on your product table:

CREATE PROC rpcProduct_Save (@ProductName varchar(50)
, @recID int

AS

begin

--usually you do this outside of the save proc :)
DECLARE @tblProduct TABLE (RecordID int IDENTITY(1,1), ProductName varchar(50))

if @recID > 0
begin
update tblProduct
set ProductName = @ProductName
where recID = @recID
end
else
begin
INSERT INTO tblProduct (
ProductName
) VALUES (
@ProductName
)
set @recID = @@Identity
end


Select @recID

--Optional, for debugging purposes I suppose
SELECT * FROM @tblProduct
end --rpcProduct_Save
Left by Brian on Aug 23, 2005 5:13 PM

# re: More new SQL Server 2005 commands: Part 2
Requesting Gravatar...
Thanks,

This is a great tip!
Left by Dave Oliver on Aug 24, 2005 6:48 AM

Comments have been closed on this topic.
Copyright © Dave Oliver | Powered by: GeeksWithBlogs.net