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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2003-09-05 : 17:52:36
|
| I have a sproc that accepts 3 parameters:CREATE PROCEDURE dbo.TSN_SO_Update (@CustomerNo Numeric, @Start_Date Numeric, @End_Date Numeric)How can I call this sproc for all of my CustomerNo's in my Customer table?EXEC dbo.TSN_SO_Update (SELECT CustomerNo, 19950101 AS Date1, 20031231 AS Date3) fails.Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-05 : 18:02:02
|
| You would have to loop through the Customer table to do it.But...SQL is a set-based language. When you use loops or cursors, you are negatively impacted by performance. So, what you should do is either rewrite the stored procedure so that it can give you results for all customers, or create a new stored procedure to do it.Tara |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-09-05 : 18:08:13
|
| So I would have to write a FETCH sproc based on my customer tables to execute the TSN_SO_UPDATE SPROC on all of my customers? I would think there would be an easier way to day that. It seems simple enough in english, "Execute this sproc for all of my customers". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-05 : 18:12:35
|
| Yeah, it seems simple, but SQL wasn't meant for this. I would not implement a looping method just to avoid rewriting a stored procedure or writing a new one. If you do implement a loop, you are going to be negatively impacted by performance.How many customers are we talking about?If you post your stored procedure code, we could help you rewrite it so that it works for all customers.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-09-05 : 18:16:53
|
| Not many customers, about 500Thanks, I know how to rewrite it for all customers. This sproc is called by a front-end process for a single customer. I just thought I could easily do it for all customers by just passing all of my customers to the sproc. Oh well. Thanks anyway. |
|
|
|
|
|
|
|
|
EXEC SPROC with ResulSet as Paramter