0

This problem is of course me not understanding SQL 2008 on an expert level but I really don't understand this scenario.

If I have the below query - and ID 9 does not exist:

declare @tmpInt int = 5
select @tmpInt=coalesce(id,0) from MyView where id=9
print @tmpInt

Why is this printing 5 as set initially!? I would expect 0 - or maybe even null?

If I do the same query on an existing row, it will naturally set the ID of that row.

UPDATE

There has been two answers already but maybe I need to clarify - how can I get the coalesce value of 0 in to my variable, if no rows exists? Is it possible? Otherwise I would need to initialize the @tmpInt variable before each query (I am using @tmpInt in many queries).

Beauvais
  • 2,149
  • 4
  • 28
  • 63
  • Frankly, initializing @tmpint before each query is probably the best, most efficient method. It's exactly what I would do. – Tab Alleman Nov 04 '14 at 14:21
  • See http://stackoverflow.com/questions/3945361/t-sql-set-versus-select-when-assigning-variables/3945448#3945448 for further details on using SET vs. SELECT for variable assignment. – Joe Stefanelli Nov 04 '14 at 14:25

3 Answers3

0

Because your select query returns no rows, no change is made to the @tmpInt.

If you made it so that it returned a row, but the value was null or 0, then the value of the variable would be changed. But no row returned = no change to the variable.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

The answer is correct, since you've initialised the @tmpInt to 5.

In case the select query would return NULL or any other value, it would have replaced the initial value of 5. But since you don't have any record where id = 9, then no row will be return.

Saying that, SQL will not set nothing value, it will rather return the value as is. Which explains the result 5, as initialised.

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
0

As others pointed out, your WHERE clause is filtering all rows, so the variable is never changed. If you need to work around this, you could use the MAX() aggregation - this will let your COALESCE() statement fire even when no rows are returned.

Example below: 

DECLARE @tmpInt INT = 5
SELECT @tmpInt = COALESCE(MAX(id),0) 
FROM 
  (
    SELECT 1 AS id -- replace this with your table
  ) s 
WHERE id=9
PRINT @tmpInt

EDIT: not sure that my sample data and extra formatting made it clear how to adapt this to your query. Maybe a side-by-side comparison would help?

Original:

select @tmpInt=coalesce(id,0) from MyView where id=9

Suggested:

SELECT @tmpInt = COALESCE(MAX(id),0) FROM MyView WHERE id = 9

Difference of five characters.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • Edited to make things a bit clearer - not sure adding five characters is more of a 'hassle' than adding a re-initialization of a variable to multiple places in a script. My overly-OCD formatting and sample data might have made it confusing ... – AHiggins Nov 04 '14 at 14:51
  • But if `coalesce(id,0)` doesn't change the `@tmpInt` why will `coalesce(max(id),0)` do it? I have tested it and it does return `0` on non-existing rows but it makes no sense to me!? – Beauvais Nov 06 '14 at 14:09
  • It works because in SQL-Server, someone made the decision that [simple aggregate functions will return data even where there are no rows](http://weblogs.sqlteam.com/jeffs/archive/2007/11/13/sql-aggregate-totals.aspx): the same thing won't work in Oracle, but SQL is configured to allow it. As to why they did so, I can't really speak to it; given [the number of questions asking Oracle to do the same thing](https://www.google.com/search?q=sql+MAX+returns+zero+if+no+rows+found), maybe they had a good reason. – AHiggins Nov 06 '14 at 14:26
  • I have changed the accepted answer to this one as it answers my question. It's still unclear to me WHY this is working except this is a special feature allowed in MSSQL. – Beauvais Nov 07 '14 at 12:02