simplifying code

Question

Post reply

simplifying code

vadimt

SSC-Addicted

Points: 479
More actions
October 19, 2007 at 7:50 am

#199241

i have a code that looks like this:
IF EXISTS ( SELECT ID FROM ... )
SET @ID = SELECT TOP 1 ID FROM ...
...some code...
ELSE
...something else....
i'm executing the same SELECT twice.
is there a way i can do it only once?
i need to check if the result exists, before returning it.
thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

declare @id int

select @id =( select top 1 n from tally where n > 5)

if @id is not null

begin

print 'yes'

print @id

end

else

begin

print 'no'

print @id

end

vadimt SSC-Addicted Points: 479 More actions · Answer 2

mrpolecat (10/19/2007)
declare @id int
select @id =( select top 1 n from tally where n > 5)
if @id is not null
begin
print 'yes'
print @id
end
else
begin
print 'no'
print @id
end

thanks.

i knew it was supposed to be something simple...

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Not sure what you're trying to do, but here's a different angle... think of it as a "set-based IF" 😀

SELECT TOP 1

CASE t.N WHEN NULL THEN 'No' ELSE 'Yes' END AS Status,

t.n

FROM dbo.Tally t,

(SELECT 1 One) d

WHERE N > 5

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vadimt SSC-Addicted Points: 479 More actions · Answer 4

Jeff Moden (10/20/2007)
Not sure what you're trying to do, but here's a different angle... think of it as a "set-based IF" 😀
SELECT TOP 1
CASE t.N WHEN NULL THEN 'No' ELSE 'Yes' END AS Status,
t.n
FROM dbo.Tally t,
(SELECT 1 One) d
WHERE N > 5

:laugh:

i think if i'll use this approach the people who will be maintaining the database will kill me at some point... unless they know about Tally tables :hehe:

its nice, but i stick to the obvious.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

No, no! The Tally table in this code was just to have a table to demonstrate from... I copied it from the previous post that MrPoleCat posted... (heh, you didn't say the same thing about his post 😀 ) Tally table doesn't even enter the picture, here...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vadimt SSC-Addicted Points: 479 More actions · Answer 6

Jeff Moden (10/21/2007)
No, no! The Tally table in this code was just to have a table to demonstrate from... I copied it from the previous post that MrPoleCat posted... (heh, you didn't say the same thing about his post 😀 ) Tally table doesn't even enter the picture, here...

well i didnt understand your example at first (i do now)... but thats just proves my point.

MrPoleCat solution was easy to understand, yours is more complicated.

its a good one, but as i said other people need to understand what i'm doing.

i dont want people calling me asking to explain something i did a year ago 😛

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Heh... that's what "embedded comments" are for 🙂 But, I understand...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)