Query Help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-07-25 : 20:28:53

Hi Guys,

I can't test this query but what you guys thinks this query looks ok to you

Create Proc Main_Sale

@Sales_ID Int,
@Sales_Date Datetime

as

Set Sales_ID = @Sales_ID
Set Sales_Date = @Sales_Date

Begin
Exec Sale_1 Sales_ID = @Sales_ID

Exec Sales_2 Sales_Date = @Sales_Date

End

Thank You.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-25 : 20:34:41

Doesn't look right. It will generate a syntax error. Are you trying to execute the two stored procs from within this stored proc? What are the signatures of the stored procedures? If the first one takes Sales_ID and the second takes Sales_Date, then your sproc should be this, but I am guessing. If you like, post the code for the two stored procs.

CREATE PROC	Main_Sale
	@Sales_ID	INT, 
	@Sales_Date	DATETIME
AS
BEGIN
	EXEC Sale_1 @Sales_ID
	
	EXEC Sales_2 @Sales_Date
END

Sonu619
Posting Yak Master

202 Posts

Posted - 2012-07-25 : 20:45:33

Awesome. Thank you for your prompt reply! Yes i am trying to execute 15 store procedure at once. Do you think i can use same logic if i want to EXECUTE 2 S.P Or 15?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-26 : 06:51:15

Yes you can use the same logic. However, you need to be careful about error handling, how the control will flow in case of exceptions etc.

With regard to exceptions, especially if you are coming from a .Net or Java background, the control flow may not be what you expect. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159274

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 09:54:55

quote:
Originally posted by Sonu619

Awesome. Thank you for your prompt reply! Yes i am trying to execute 15 store procedure at once. Do you think i can use same logic if i want to EXECUTE 2 S.P Or 15?

why do you need 15 procs? if its not reusable logic better to keep it inside same proc with proper error handling for each block

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 10:12:14

CREATE PROC Main_Sale
@Sales_ID INT,
@Sales_Date DATETIME
AS

begin try
EXEC Sale_1 @Sales_ID
EXEC Sales_2 @Sales_Date
end try
begin catch
raiserror('I have failed', 16, -1)
end catch

have a look at
http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html

For logging and error handling.
I would use this in the control procedure and each of the children so that you know what has happened, how long things have taken and how many rows affected - as well as how far the process has got.
You might think of this as a thread of proceses to be carried outif it is to be scheduled and implement something like
http://www.nigelrivett.net/Products/DWBuilder/BatchControlSystem.pdf

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources