Calling a stored procedure within another procedur

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calling a stored procedure within another procedur

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-24 : 15:18:00

Hi All,

I am dealing with a stored procedure called "p_GeneratePOS" that takes 4 arguments to execute.
The arguments are passed and stored in the following variables.

@i_dt_StartDate DateTime
@i_dt_EndDate DateTime
@i_i_BackDateWeeks int
@i_b_Updating bit {0,1}

The exec statement look like the following (this is just an example);
exec p_GeneratePOS '2009-04-01', '2009-09-08', 3, 1;

Within the stored procedure I want to set the values of these varaiables to the following. So that whenever the stored procedure executes, these values are always there.

set @i_dt_StartDate = (select max(date)-21 from RawDB.dbo.WeeklySalesHistoryXML_$)
set @i_dt_EndDate = (select max(date)+6 from RawDB.dbo.WeeklySalesHistoryXML_$)
set @i_i_BackDateWeeks =3
set @i_b_Updating=1

I can do that, it is not a big deal. The problem is that I have been told by my develeopment team that I cannot modify the "p_GeneratePOS" stored procedure, and instead I can create another stored procedure (lets call it "p_callPOS") which will call the "p_GeneratePOS" stored procedure with the above values.
How to do that? How to create another stored procedure "p_callPOS" which will call the stored procedure "p_GeneratePOS" with the above values?

Thanks a million in advance.

Zee

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 15:29:23

[code]
create proc p_callPOS
as

declare @i_dt_StartDate datetime
,@i_dt_EndDate datetime
,@i_i_BackDateWeeks int
,@i_b_Updating bit

set @i_dt_StartDate = (select max(date)-21 from RawDB.dbo.WeeklySalesHistoryXML_$)
set @i_dt_EndDate = (select max(date)+6 from RawDB.dbo.WeeklySalesHistoryXML_$)
set @i_i_BackDateWeeks =3
set @i_b_Updating=1

/*
--This may be a little more efficient:

select @i_dt_StartDate = max(date)-21
,@i_dt_EndDate = max(date)+6
,@i_i_BackDateWeeks =3
,@i_b_Updating=1
from [RawDB].[dbo].[WeeklySalesHistoryXML_$]
*/

exec p_GeneratePOS
@i_dt_StartDate
,@i_dt_EndDate
,@i_i_BackDateWeeks
,@i_b_Updating
go
[/code]

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-24 : 15:46:10

Thanks TG.

Looks good. I have not tried it yet but before I do let me ask you that, is this a problem since the variables used within both stored procedure are exactly same in terms of name and data type. Please let me know.

Thanks....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 16:00:45

No, it's not a problem but it would avoid confusion to do it this way (which I prefer anyway :)


exec p_GeneratePOS 
       @i_dt_StartDate = @i_dt_StartDate
       ,@i_dt_EndDate = @i_dt_EndDate
       ,@i_i_BackDateWeeks = @i_i_BackDateWeeks
       ,@i_b_Updating = @i_b_Updating

Be One with the Optimizer
TG

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-09-24 : 21:39:17

Thanks TG...it worked :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources