SET or SELECT

  • Not sure if this is the correct forum. Maybe 'Performance tuning' would be a better place to ask this. Anyway, here it goes.

    In our firm, we use both the SET and SELECT statements to set the value of a variable in a procedure.

    e.g.

    DECLARE @iVar int

    SET @iVar = 2

    --Or also

    SELECT @iVar = 2

    Some of us prefer the SET command, while others always use SELECT. Are there any arguments (besides personal preference) to choose either one?

  • No there is no comparison of the two as to any improvements over the other. It is kind of like SUB versus FUNCTION in VB, in many case either can be used, but in some only one or the other can be used. Such is with SELECT when getting multiple lines of data from a table. It ultimately comes down to personal preference.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • I use set when I'm assigning a value to a variable directly or from a passed parameter, select if the value is coming from a table. Jimmy Nilsson had a great article on it in the Feb 2002 issue of SQL Server Professional (www.pinnaclepublishing.com/sql).

    Andy

  • SET applies to built in function, like:

    SET NOCOUNT ON;SET ROWCOUNT 4 etc.

    For SET/SELECT in other cases - SELECT is more useful ( you can't use SET in many cases) so I would use SELECT to avoid problems

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply