Hi

Which one of the following alternatives are the most correct to use in the
below scenario.

DECLARE @id int, @total int;

SELECT @id = t.Id, @total = t.Amount
FROM Table2 t
WHERE t.Table1Id = @table1id AND t.Key = @key;

Alt 1.
IF(@id > 0)
   ...

Alt 2.
IF(@total > NULL)
   ...

Alt 3.
IF(@total IS NOT NULL)
   ...

Alt 1.
IF(@id > 0)
    ...

Alt 2.
IF(@id > NULL)
    ...

Alt 3.
IF(@id IS NOT NULL)
    ...

Show quoteHide quote

Depends on what you want.  First, I assume there can be at most one row
WHERE t.Table1Id = @table1id AND t.Key = @key, otherwise this is ambigous
and that statement should be corrected.

Alt 1 will be true if a row exists in Table2 with Table1Id=@table1id AND
t.Key = @key AND Table1Id.Id > 0 for that row

Alt 2 will NEVER be true, there is no possible value that can be > NULL

Alt 3 will be true if a row exists in Table2 with Table1Id=@table1id AND
t.Key = @key

Tom

Show quoteHide quote

If all you care about is whether a row exists, and aren't actually going to
use the variables for anything, then the following can not possibly perform
any worse than any of your solutions:

IF EXISTS
(
    SELECT 1
        FROM Table2
        WHERE Table1Id = @table1id
        AND Key = @key
)
    ...



Show quoteHide quote

Can Table2.id be validly zero?
Can Table2.Amount be validly zero?
Does Table2.Amount allow NULLs?

What are you trying to find out?  Are you trying to discover whether
the SELECT matched a row, or no rows?  Are you trying to find out if
the data in the row that was returned is valid?

The one thing I can say for certain is is that the second alternative
is of no use.  Nothing is greater than NULL.  (Or less than NULL.  Or
equal to NULL, not even another NULL.  Or not equal to NULL.)

If all you want to know is whether the SELECT returned anything, the
usual test is against @@rowcount.  Remember that @@rowount is only
valid IMMEDIATELY after the execution.

Roy Harvey
Beacon Falls, CT

On Thu, 24 Aug 2006 10:26:02 -0700, Senna
<Se***@discussions.microsoft.com> wrote:

Show quoteHide quote

Will go with Roys alternative with @@rowcount.

Thanks to both of you for your answers. =)



DECLARE @id int, @total int;

SELECT @id = t.Id, @total = t.Amount
FROM Table2 t
WHERE t.Table1Id = @table1id AND t.Key = @key;

Alt 1.
IF(@id > 0)
   ...

Alt 2.
IF(@total > NULL)
   ...

Alt 3.
IF(@total IS NOT NULL)
   ...

Alt 4.
IF(@@rowcount > 0)
   ...


Show quoteHide quote

Need Help With a Simple Query
Error converting data type varchar to numeric
BEGIN WHILE EOF
EncryptByCert problem
The pains of XML programming