executing stored procedure within a stored proc

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)

executing stored procedure within a stored proc

Author

Topic

carmitage
Starting Member

8 Posts

Posted - 2008-10-07 : 11:19:27

I want to run a stored procedure within another stored procedure based on a variable value that is input. How can I accomplish this?

ALTER procedure [dbo].[sp_QRM_AL_VALUATION_ANALYSIS]

@QRM_DATABASE varchar(8),
...
as
begin

...
select CASE @QRM_DATABASE
WHEN 'PROD' THEN EXEC sp_QRM_12_Account_Rollup_PROD
WHEN 'INCOME' THEN EXEC sp_QRM_12_Account_Rollup_INCOME
END

...

Also, it may be a different topic, however, in the same stored procedure I also need to choose tables to update/insert into based on the same @QRM_DATABASE input variable. How can this work?

INSERT INTO alm.dbo.QRM_VALUATION_ANALYSIS_TEST_TEMP
SELECT @QRM_DATABASE,
@REFRESH_DATE,
VW_RUN_DESC.COMPID,
...
FROM
(select CASE @QRM_DATABASE
WHEN 'PROD' THEN FSPROD.QRMDB8_1228L.dbo.vw_panl_scn_cube WHEN 'INCOME' THEN FSINCOME.QRMDB8_INCOME.dbo.vw_panl_scn_cube
end)
INNER join (select CASE @QRM_DATABASE
WHEN 'PROD' THEN FSPROD.QRMDB8_1228L.dbo.VW_RUN_DESC WHEN 'INCOME' THEN FSINCOME.QRMDB8_INCOME.dbo.VW_RUN_DESC
end)

ON
VW_RUN_DESC.compid = vw_panl_scn_cube.compid and
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 11:53:40

put it in a variable and the use it with sp

if  @QRM_DATABASE ='PROD'
insert desttable
EXEC sp_QRM_12_Account_Rollup_PROD
if  @QRM_DATABASE ='INCOME' 
insert desttable
 EXEC sp_QRM_12_Account_Rollup_INCOME

for the next part, you need dynamic sql, you cant use CASE WHEN to detrmine what table you need to join to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:04:51

And its not a good option to pass your object name as a parameter and using dynamic sql. why do you want to do this way?

carmitage
Starting Member

8 Posts

Posted - 2008-10-07 : 13:18:25

I have several insert and update statements in the stored proc that are based on which database the user chooses.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-07 : 13:20:31

quote:
Originally posted by carmitage

I have several insert and update statements in the stored proc that are based on which database the user chooses.

You shouldn't need dynamic SQL for this. It suffers from weakened security and potential performance issues as compared to regular SQL inside a stored procedure. Avoid dynamic SQL wherever possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources