June 15, 2005 at 7:30 am
I am using the below code within a Stored Procedure to see if a number exists in a column in a table and depending on whether it does or not I am doing an Insert.
Problem is I keep getting this error message when I try to save the Stored Procedure "Incorrect syntax near '=', Incorrect syntax near the keyword 'Else'".
Anyone any ideas what the problem is?
Thanks macca.
IF EXISTS (SELECT @tempNum = Num FROM GenNum WHERE @serOff = SO)
BEGIN
SET @tempNumTwo = @tempNum + 1
INSERT INTO GenNum(SO, Num)
VALUES(@serOff,@tempNumTwo)
END
ELSE
BEGIN
INSERT INTO GenNum(SO, Num)
VALUES(@serOff,@tempNum)
END
June 15, 2005 at 7:34 am
if EXISTS (Select * from GenNum where So = @Seroff)...
June 15, 2005 at 8:05 am
Just a question. Woudn't (Select 1 From GenNum where So = @Seroff) would be more efficient?
June 15, 2005 at 8:13 am
Apparently not because EXISTS short-circuits
June 15, 2005 at 8:44 am
Just to elaborate on David's answer. Exists returns true or false. As soon as the query would starts to return data, the exists exit.
June 15, 2005 at 3:30 pm
>>Just a question. Woudn't (Select 1 From GenNum where So = @Seroff) would be more efficient?
To elaborate more - you can always spot the old Sybase T-SQL developers, because back at around version 4.x of Sybase (and maybe 6.5 of Sql Server ?), it actually could be more efficient in terms of what the optimizer chose as a query plan, to "Select SomeConstant" instead of "Select *" in an Exists condition.
In those earlier versions, the optimizer might choose to ignore certain indexes if it thought it had to perform expensive bookmark lookups to retrieve all columns for the "*", even though the contents of those columns was irrelevant to determining "existence".
Anyone who coded T-SQL in those days probably still supersticiously codes "Select 1" instead of "Select *" .
June 16, 2005 at 2:05 am
It doesn't do any harm to code SELECT 1 FROM etc
Playing devils advocate I would say that
a) It emphasises that this SELECT statement is for a specific purpose
b) SELECT * is bad. Irrelevant though it may be in the EXISTS statement get out of the habit of using it. Never let a developer see you using SELECT *.
June 16, 2005 at 7:09 am
Is it that hard to get developpers to stop doing that??? I didn't have problems to adapt to that change.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply