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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems