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 |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2006-02-09 : 12:00:53
|
| I wrote a sql statement as bellow:select * from tblOrder where OrderID in (select * from tblNewOrder where Order_City = 'New York')It works fine. Now, I want to change to store procedure. How to code it? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-02-09 : 12:14:17
|
First thing to do is to change it from Select * to an actual field list, just to get out of the habit before you learn to use select * all the time..Then you just:create procedure usp_NewYorkOrdersasselect <fieldlist> from tblOrder where OrderID in (select OrderID from tblNewOrder where Order_City = 'New York') Then to call it just runexec usp_NewYorkOrders |
 |
|
|
Sun Foster
Aged Yak Warrior
515 Posts |
Posted - 2006-02-09 : 12:26:44
|
| Thank you.If I use two sp, like bellow, is it fast than one sp?sp1: select <fieldlist> from tblOrder where OrderID in (sp2 output)sp2: select OrderID from tblNewOrder where Order_City = 'New York' |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-09 : 13:07:18
|
how about a single sp that does this?select <a.fieldList>from tblOrder ajoin tblNewOrder b on b.orderid = a.orderidwhere b.order_city = 'New York' Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-09 : 14:06:43
|
| Can there be duplicates in tblNewOrder? If so it will return duplicate rows from tblOrder (which RickD's solution avoids)Seems unlikely with reasonably normalised data etc., but just thought I'd mention it!Kristen |
|
|
|
|
|
|
How to use sore procedure to do it?