Re: which is better... SET or SELECT?

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 08/02/04 

Date: Mon, 2 Aug 2004 18:31:15 +0530

Also with SELECT we can introduce some intuisive looping behaviour.
As in

   CREATE TABLE #tmp (a int)
   INSERT #tmp VALUES(1)
   INSERT #tmp VALUES(2)
   INSERT #tmp VALUES(3)
   INSERT #tmp VALUES(4)
   INSERT #tmp VALUES(5)

   DECLARE @p int

   SELECT @p = isnull(@p, 1) * a FROM #tmp

   SELECT @p

    Here @p become 120, the product of all values of a.
    We can observe that the assignment is carried out once
    for each row of in the table #tmp.

   The same logic is used in the varchar concatenation technique shown
below.

    DECLARE @vchAuthors VARCHAR(400)
    SET @vchAuthors = ''
    SELECT @vchAuthors = @vchAuthors +
    CASE WHEN @vchAuthors = '' THEN '' ELSE ', ' END + au_lname
    FROM authors

    SELECT @vchAuthors 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:ObednZbR7r0elZPcRVn-uA@giganews.com...
> One important difference between SET and SELECT for variable assignment is
> the behaviour when no rows are returned from the SELECT statement. If
> variables are assigned in the SELECT then the value of the variables will
> remain unchanged. Example:
>
> DECLARE @id INTEGER
>
> SET @id = 0
>
> SELECT @id = id
>  FROM sysobjects
>  WHERE name = 'does not exist'
>
> SELECT @id -- Returns Zero
>
> SET @id=
>  (SELECT id
>   FROM sysobjects
>   WHERE name = 'does not exist')
>
> SELECT @id -- Returns NULL
>
> -- 
> David Portas
> SQL Server MVP
> --
>
>

Relevant Pages

  • Re: program bug
    ... declare an int? ... Or should I have done this int a=0; ... assignment. ... garbage, ...
    (comp.lang.c)
  • Re: Odd pivot table type query
    ... you will have to make use of procedural code to ... SEQ_NUM int, ... declare @seq_num int ... select * from #tmp ...
    (microsoft.public.sqlserver.mseq)
  • RE: Accessing value from dynamic SQL
    ... DECLARE @table SYSNAME ... CREATE TABLE #tmp (x INT) ... > The dynamic statement will execute and return a value. ...
    (microsoft.public.sqlserver.programming)
  • Re: Frequency distribution SQL statement
    ... declare @tmp table ... insert @tmp values ... > @min_value int, ... >> Dean, ...
    (microsoft.public.sqlserver.programming)
  • Re: loop question
    ... DECLARE @i INT ... CREATE TABLE #tmp ... > how can i create a loop to run 7 times? ...
    (microsoft.public.sqlserver.programming)