SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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]
Code

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
Code

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
Code

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
Code

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

pass parameter value between stored procedures in SQL Server

I hope this SQL tutorial will be helpful showing how to pass parameter values between stored procedures in SQL Server for developers



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.