Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-07-17 : 08:58:46
|
create table page47(pk char(1) not null primary key)declare @blah int, @pk char(1)set @pk = 'a' I want a single select statement to set @blah to 0 if there is no match on page47.pk = @pk, and 1 if there is a match. If my FROM clause only refs page47, and I do the comparison in a WHERE clause, I can't set the variable in my SELECT, since it will produce and empty rowset. Someone posted a neat trick using a join to a derived table to force the query to generate a rowset, but I can't seem to find/remember it....<O> |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-07-17 : 09:11:11
|
| How about thisselect @blah=case isnull(count(1),0)from #page47where pk=@pkselect @blahHTH-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-17 : 09:14:48
|
| Yeah, that works, but I am looking for the trick using a derived table.....I though it was something like[code]select ....from (select 'page47' as col) a left join #page47 on a.col = 'page47'[code] ...its just an idle curiosity....[edit]BINGO, rob .... thanks[/edit]<O>Edited by - Page47 on 07/17/2002 09:16:42 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-17 : 09:18:02
|
| Nazim, you see when you start adding more tables into the from clause, the other columns become invalid because they are not contained in the implied aggregate from the count(*)....<O> |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-07-17 : 09:28:53
|
I didnt actually noticed it was Page47 who posted it.was just focussing on the problem at hand. Man! now i days i dont post much , actually i hold on my posts .i know you will answer it much better then i will and i can pick something from it  . i saw the link Rob provided , pretty intresting trick.-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
|
|
|
|
|
Someone had a neat trick, but I can't find it....