SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 SQL Statement for IF Clause
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

ccomstock2
Starting Member

18 Posts

Posted - 01/07/2003 :  13:12:56  Show Profile
I have a stored procedure to update a table with invoice information. There are a bunch of calculation being done and the example below is to calcuate the prepaid shipping for a customer. If they use it I need to do one calcuation and if they don't I need to do another calcualtion to figure out their total charges. @customerID is a parameter passed to the sproc but this example below gives me a syntax error. Does anyone have any ideas what is wrong with it ? Thanks

IF(@customerID exists in(SELECT customerID FROM tblPrePaidShipping))
BEGIN
.
.
.
END

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/07/2003 :  13:17:36  Show Profile  Visit jsmith8858's Homepage
Get rid of the "exists" and you should be all set.

Just use "Customer IN (Select ....)"





- Jeff
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 01/08/2003 :  02:03:18  Show Profile  Visit mohdowais's Homepage
....
OR

IF EXISTS (SELECT CustomerID FROM tblPrePaidShipping WHERE CustomerID = @CustomerID)


OS

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/08/2003 :  03:18:09  Show Profile  Visit nr's Homepage
or better
IF EXISTS (SELECT * FROM tblPrePaidShipping WHERE CustomerID = @CustomerID)


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/08/2003 :  06:53:19  Show Profile
Why is that better nr?

Jay White
{0}
Go to Top of Page

Peter Dutch
Posting Yak Master

Netherlands
127 Posts

Posted - 01/08/2003 :  09:48:08  Show Profile
because if you use 'select *' in an IF EXISTS query, the queryprocessor can decide which column to use.




Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/08/2003 :  10:02:25  Show Profile  Visit nr's Homepage
i.e. the best index

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/08/2003 :  12:14:57  Show Profile
does the same apply to 'select 1' ? I would think so.


Jay White
{0}
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/08/2003 :  12:25:26  Show Profile  Visit nr's Homepage
unfortunately yes (in this version anyway).
I really hate seeing if exists (select 1 from ... )
but I have a lot of unreasonable opinions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/08/2003 12:26:58
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000