Execute Stored Procedure passing Parameter value to another Procedure
In this SQL tutorial, I will demonstrate how to call a stored procedure within an other stored procedure and pass values from one to another. And I'll also create a solution for choosing lucky lotto numbers using two SQL stored procedures.
OUTPUT hint used with stored procedure parameters enables passing parameter values between two stored procedures which is calling the other one within its source codes.
First of all, as a base SQL developers should create a sql script which generates an output value. For to use in this T-SQL tutorial, I'll create a SQL code which will choose a random number within the given number range.
declare @range_min int = 1
declare @range_max int = 49
declare @lucky int
set @lucky = FLOOR( RAND() * (@range_max - @range_min + 1) ) + 1
select @lucky as [random number]
Each time an SQL developer executes the above script, a random number between the given range will be selected and displayed as the winner lucky number.
The next step is to wrap this SQL code into a SQL stored procedure.
create procedure sp_GetLuckyNumber (
@range_min int,
@range_max int,
@lucky int OUTPUT -- passes its value outside as marked OUTPUT
)
as
set @lucky = FLOOR( RAND() * (@range_max - @range_min + 1) ) + 1
go
Here is how above stored procedure can be executed. Please see that the @i parameter of the stored procedure is marked with OUTPUT hint. OUTPUT hint of the stored procedure enables passing its value from inside SP code to outside SP.
declare @i int
exec sp_GetLuckyNumber 1, 100, @i output
print @i
Let's now create a second SQL Server stored procedure which will demonstrate a well-known Turkish Lotto game called Sayısal Loto Although there are different sub-solutions within the SQL stored procedure for requirements specific to Sayisal Loto game, let's concentrate into passing parameter value from inner stored procedure to outer stored procedure.
In the WHILE loop, developers will see inner stored procedure is executed with one of its parameter is assigned as an OUTPUT parameter. This output parameter enables value passing from inside of the stored procedure to outside of the procedure.
create procedure sp_SayisalLoto
as
declare @t as table (luckynumber int)
declare @set varchar(max)
declare @counter int
set @counter = 6
while @counter > 0
begin
declare @i int
-- get output of the sp_GetLuckyNumber stored procedure into @i parameter
exec sp_GetLuckyNumber 1, 49, @i OUTPUT
if not exists(select * from @t where luckynumber = @i)
begin
insert into @t select @i
set @counter = @counter - 1
end
end
select
-- @set = CONCAT(@set + ', ', cast(luckynumber as varchar(10)))
@set = ISNULL(@set + ', ','') + cast(luckynumber as varchar(10))
from @t
order by luckynumber
select @set as [lotto numbers]
go
If your SQL Server version is SQL Server 2012 or SQL Server 2014, new SQL function CONCAT can be used. In order to prevent a failure on previous version of SQL Server, I commented the source code line where SQL CONCAT function is used and supplied an alternative sql code.
Here is the output of Sayisal Loto stored procedure execution
I hope this SQL tutorial will be helpful showing how to pass parameter values between stored procedures in SQL Server for developers