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

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 08/02/04 

Date: Mon, 2 Aug 2004 10:24:34 +0100

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: which is better... SET or SELECT?
    ... > One important difference between SET and SELECT for variable assignment is> the behaviour when no rows are returned from the SELECT statement. ... > DECLARE @id INTEGER ... > FROM sysobjects ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers And Information Schema
    ... FROM sysobjects ... WHERE xtype = 'TR' ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver)
Loading