Calling one stored procedure within another stored procedure using variables from first stored procedure

Question

I have a stored procedure say @Create_Dummy1 which is being passed a variable. This is declared as @Dummy_Variable1 in this stored procedure.

Next I need to call another stored procedure @Create_Dummy2 from @Create_Dummy1. I need to pass @Dummy_Variable1 in the exec statement.

But if I try to do this the string @Dummy_Variable1 is only being passed instead of the value it holds.

Solution 1

I'm executing procedures inside other procedures like this:

DECLARE @childResult int, @loaErrorCode int, @loaErrorMessage varchar(255) 
EXEC @childResult = [dbo].[proc_sub_getSomething] @schemes_id = @foo_schemes_i, @errorCode = @loaErrorCode OUTPUT , @errorMessage = @loaErrorMessage OUTPUT

Should it still not work you should edit your question to show your exact code.

Solution 2

This should work:

create procedure Create_Dummy1
(
    @Dummy_Variable1  int
)
as 

exec Create_Dummy2 @Dummy_Variable1

Go

And

create procedure Create_Dummy2
(
    @Dummy_Variable1  int
)
as 

Select * From yourTable WHERE tableColumn = @Dummy_Variable1

And this is how you call it:

exec Create_Dummy1 1

Hope this helps.

Comments

[Shruti]

This did not work out. This is exactly what i tried. It passed the string [@Dummy_Variable1] instead if value held in this variable. But let me specify that that value stored in this variable [@Dummy_Variable1] is a string

[Shruti]

dbo.Stored_procedure_name Worked.