Rip's Domain

Don’t use SELECT when you mean SET

Posted in ColdFusion, SQL by rip747 on October 5, 2006

This is just a pet pev of mine that I’ve seen about 95% of people do when writing MSSQL stored procedures

CREATE PROCEDURE myproc
AS
SET NOCOUNT ON
DECLARE @i int
SELECT @i = DATEPART(hh,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO

There is nothing wrong with the above SP, it will compile and run just fine. The problem I have with it is using the SELECT keyword to set a variable when the SET keyword will work just fine. Not only that, but you don’t have to remember to use SET NOCOUNT ON declaration. So here is the same SP using the SET keyword

CREATE PROCEDURE myproc
AS
DECLARE @i int
SET @i = DATEPART(hh,GETDATE())
RETURN @i
GO

A lot less typing, cleaner looking and easier to read if you ask me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: