Passing Information between Store Procs 1

[Robinstwitcher]

I have a number of store procedures that run in sequence as part of data load process each month. Each one requires a common value to extracted from a table based on a criteria (e.g. Current Month). Can I create a variable in the first store proc and pass it to the remaining or do I have to recreate it each time?

 

[AlexCuse]

How are you running the sp's? I think there are a few ways you can do this, but it all depends on how they are called.

Good Luck,

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[Robinstwitcher]

I currently have one overall store proc that calls the others - am open to suggestions if this is not the right solution!?

 

[AlexCuse]

if that is the case, you could pass your parameter in to 'Master' stored proc, then call the others using the parameter value taken by 'Master'?

I hope this makes sense.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Robinstwitcher]

Alex, yes it does make sense. However my abilities as yet have not stretched to passing parameters between Store Procs!! can you help?

 

[AlexCuse]

are your sp's now configured to accept a parameter?

If so, you call them using something like:

exec dbo.procName 'some string', right?

you then call your sp's inside the 'master' proc using something like

exec dbo.procName (parameter name from 'master' proc)

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Robinstwitcher]

Alex,

The Master store proc looks like the following. I don't believe I have configured them to accept a parameter?

CREATE PROCEDURE [dbo].[usp_ControlPanel] AS

exec usp_InsertData

exec usp_UpdateData

exec usp_TransferData

GO

the code within usp_InsertData is

CREATE PROCEDURE [dbo].[usp_InsertData] AS

INSERT INTO tempTable (col1, Col2)

SELECT (column1, column2)

FROM Import

The parameter is strMonth

 

[AlexCuse]

Ok, to change usp_ControlPanel you would do this:

CREATE PROCEDURE [dbo].[usp_ControlPanel] (@strMonth varchar(10)) AS

exec usp_InsertData @strMonth

exec usp_UpdateData @strMonth

exec usp_TransferData @strMonth

You then need to configure your insert, update, and transfer procs to accept the variable @strMonth (which will accept variable from your front end. I am guessing a VB front end/ASP page?). That is the piece of code following CREATE PROCEDURE procedure_name.

You will also need to add this to queries in the three procs where needed -

where (appropriate column name) = @strMonth

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Robinstwitcher]

Spot on! - thanks for your help