Dynamic Execution Path (stored procedure) 1

[zemp]

I am trying to put together a stored procedure with a dynamic execution path based on criteia it collects.

I am using a cursor to get the basic OrderID information and an 'Ordertype'. Ther type determines which other tables we need to access for scheduling and dispatching. These will be within separate stored procedures.

Below is what I have so far with the psuedocode that I am looking for.

DECLARE	@OID int,
	@OT tinyint

DECLARE OrdCur CURSOR STATIC LOCAL FOR
	SELECT top 10 OrderID, OrderType FROM THDIS.dbo.Orders WHERE (PostDate<='12/31/2006') 
OPEN OrdCur

Fetch first from OrdCur into @OID,  
	
while @@fetch_status = 0
	BEGIN

      		-- psuedocode for what I am looking for.
		CASE @OT		    
                    when 1 then SPR1 @OID
		    when 2 then SPR2 @OID
		    when 3 then SPR3 @OID
		    else SPR4 @OID
		END
                -- end of psuedo

		-- Fetch the next record.
		FETCH NEXT FROM OrdCur into @OID, @OT

	END

CLOSE OrdCur
DEALLOCATE OrdCur

My other option is to put this into the apps code and call the SPR's from the app.

Thanks.


zemp

 

[ca8msm]

Why don't you just write a query (inside an "if" statement) for each order type? It will be much faster than using a cursor.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[zemp]

I tought about that but I have to get an original recordset of maybe hundreds of records and loop through them to do some appropriate processing.

The specific tables that I need to process will depend on the type of order.

For example:

Get the orders ID and the order type values
First record...
If Type = 1 then process with tables 1- 5
If Type =2 then process with tables 6-8
etc.
Next record...
If Type = 1 then process with tables 1-5
If Type =2 then process with tables 6-8
etc.
Next record...

I am not sure if I am following you. Are you saying that I don't need a cursor to loop through records in a stored procedure? Please clarify.

zemp

 

[ca8msm]

Are you saying that I don't need a cursor to loop through records in a stored procedure? Please clarify.

No, I'm not saying that you don't need a cursor if you do actually need to loop through each row. I was just questioning whether there was actually a need to loop through every row, or whether you could do what you wanted via a set based approach.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

zemp,

If I understand correctly, you want to call different stored procedures depending on the Type value. If this is true, then you will need to use IF, not CASE. Like this...

If @OT = 1
  Execute SPR1 @OID
Else If @OT = 2
  Execute SPR2 @OID
Else If @OT = 3
  Execute SPR3 @OID
Else
  Execute SPR4 @OID

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[zemp]

Thank You both for your input and time.

George - that's exactly what I needed. Thanks again.

zemp

 

[SQLSister]

One thing you need to learn about SQL Server is that it is a poor idea to loop through records one at a time. SQL Server is optimized to work with groups of records.

Suppose you have 10000 records you need to process and you have two stored procs that you can use based on the value of one of the fields in the 10000 records. If these stored procs only process one record at a time, your cursor will have to fire 10000 times and excute the individual procs (whichever is chosen by the cursor) 10000 times.

This is extremely time conmsuming and uses many more network and server resources than needed. Instead you should write new procs that are not performing an action on an individual record but which insert a group of records instead. This is a case when reuse of code is a very bad idea. Your process can move from taking minutes or even hours to taking milliseconds simply by using a better method and not reusing code for a purpose it was not written for.

You might not even need to call procs if all you are doing is one statement for each possible branch. Not knowing what your proc does it is hard to say how to change it to make it work better.

However, lets take a common case. You have a cursor based on the following select statement:

select test, test2, test3 from table1

The cursor chooses one of two sprocs to run based on the value of test3. Each proc does a simple insert to another table, but each proc inserts to a differnt table. The code of each proc is in the form of:

insert [tablename] (field1, field2)
values @test, @test2

Set-based code to replace this mess and process faster would be something like:

insert into table2 (test1, test2)
select test1, test2 from table1 where test3 = 'y'

insert into table3 (test1, test2)
select test1, test2 from table1 where test3 = 'n'

This would be significantly faster than reusing sps intended for inserting only one record at a time. It is also simpler to write and maintain.

"NOTHING is more important in a database than integrity." ESquared