Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.05 seconds. Powered By: Snitz Forums 2000