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 |
|
ccomstock2
Starting Member
18 Posts |
 Posted - 2003-01-07 : 13:12:56
|
| 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 ? ThanksIF(@customerID exists in(SELECT customerID FROM tblPrePaidShipping)) BEGIN . . . END |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-07 : 13:17:36
|
| Get rid of the "exists" and you should be all set.Just use "Customer IN (Select ....)"- Jeff |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-01-08 : 02:03:18
|
| ....OR IF EXISTS (SELECT CustomerID FROM tblPrePaidShipping WHERE CustomerID = @CustomerID)OS |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 03:18:09
|
| or betterIF 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. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-08 : 06:53:19
|
| Why is that better nr?Jay White{0} |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-01-08 : 09:48:08
|
| because if you use 'select *' in an IF EXISTS query, the queryprocessor can decide which column to use. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 10:02:25
|
| 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. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-08 : 12:14:57
|
| does the same apply to 'select 1' ? I would think so.Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 12:25:26
|
| 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 |
|
|
|
|
|
|
SQL Statement for IF Clause