Understanding the Problem and Stored Procedures in SQL Server
=============================================================
As developers, we often find ourselves dealing with complex logic and calculations within stored procedures. In this article, we’ll delve into a specific issue where an executable result from a stored procedure is not being assigned to a variable, resulting in a default value of 0.
The Problem: Assigning SP Result to Variables and Summing the Result
The provided question highlights an issue with both stored procedures returning an int value but failing to produce the expected sum. The main goal here is to keep the results separate and then calculate their total at the end, allowing for modular child procedures.
-- Child procedure QSP_getCount1
if OBJECT_ID('dbo.QSP_getCount1', 'P') is not null
drop procedure [dbo].[QSP_getCount1]
go
create procedure [dbo].[QSP_getCount1] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a result set with one row and one column
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col2 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col3 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col4 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col5 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result
return @NullTotal;
end;
The Solution: Assigning an Exec Result to a SQL Variable
To resolve this issue, we need to understand how stored procedures work in SQL Server. A stored procedure is essentially a program that can be executed multiple times with different input parameters.
One of the key differences between a SELECT statement and a stored procedure is how they return values. When a SELECT statement is used, it returns a result set containing rows and columns.
In contrast, when an executable result from a stored procedure is not assigned to a variable, the default value returned is 0.
To assign an executable result from a stored procedure to a SQL variable, we use the RETURN statement. The RETURN statement allows us to return values from a stored procedure.
-- Child procedure QSP_getCount1
if OBJECT_ID('dbo.QSP_getCount1', 'P') is not null
drop procedure [dbo].[QSP_getCount1]
go
create procedure [dbo].[QSP_getCount1] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a result set with one row and one column
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col2 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col3 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col4 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col5 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result using the RETURN statement
return @NullTotal;
end;
Alternative Approach: Returning a Scalar Value
Another way to assign an executable result from a stored procedure to a SQL variable is by returning a scalar value.
To do this, we need to modify our stored procedure to use a single SELECT statement with no columns specified:
-- Child procedure QSP_getCount1
if OBJECT_ID('dbo.QSP_getCount1', 'P') is not null
drop procedure [dbo].[QSP_getCount1]
go
create procedure [dbo].[QSP_getCount1] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a scalar value with one row and no columns
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result using the RETURN statement
return @NullTotal;
end;
The Correct Solution: Assigning an Exec Result to a SQL Variable
In both approaches, we assign the executable result from the stored procedure to a SQL variable.
However, in order to illustrate this concept correctly, let’s revisit our original code snippet with some modifications:
-- Parent procedure
declare @Result1 int = 0,
@Result2 int = 0,
@Total int = 0,
@projectID int,
@periodID int;
exec @Result1 = [dbo].[QSP_getCount1] @projectID = 1,
@periodID = 12
exec @Result2= [dbo].[QSP_getCount2] @projectID = 1,
@periodID = 12
-- Return the calculated result using the RETURN statement
set @Total = @Result1 + @Result2;
select @Total;
-- Child procedure QSP_getCount1
if OBJECT_ID('dbo.QSP_getCount1', 'P') is not null
drop procedure [dbo].[QSP_getCount1]
go
create procedure [dbo].[QSP_getCount1] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a result set with one row and one column
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col2 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col3 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col4 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col5 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result using the RETURN statement
return @NullTotal;
end;
-- Child procedure QSP_getCount2
if OBJECT_ID('dbo.QSP_getCount2', 'P') is not null
drop procedure [dbo].[QSP_getCount2]
go
create procedure [dbo].[QSP_getCount2] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a result set with one row and one column
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col2 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col3 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col4 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col5 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result using the RETURN statement
return @NullTotal;
end;
The Correct Approach: Assigning an Exec Result to a SQL Variable
The final step is to correctly assign an executable result from our stored procedure to a SQL variable. To do this, we simply use the RETURN statement.
In the parent procedure:
-- Child procedure QSP_getCount1
if OBJECT_ID('dbo.QSP_getCount1', 'P') is not null
drop procedure [dbo].[QSP_getCount1]
go
create procedure [dbo].[QSP_getCount1] @projectID int,
@periodID int
as
begin
declare @NullTotal int = 0;
-- Select statement returns a result set with one row and one column
select @NullTotal = case when col1 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col2 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col3 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col4 is not null then @NullTotal + 1 else @NullTotal end,
@NullTotal = case when col5 is not null then @NullTotal + 1 else @NullTotal end
from tablename
where projectID = 1005 and periodID = 210;
-- Return the calculated result using the RETURN statement
return @NullTotal;
end;
In the parent procedure, we can simply use the RETURN statement to calculate the total:
-- Parent procedure
declare @Result1 int = 0,
@Result2 int = 0,
@Total int = 0,
@projectID int,
@periodID int;
exec @Result1 = [dbo].[QSP_getCount1] @projectID = 1,
@periodID = 12
exec @Result2= [dbo].[QSP_getCount2] @projectID = 1,
@periodID = 12
-- Return the calculated result using the RETURN statement
set @Total = @Result1 + @Result2;
select @Total;
This corrected approach ensures that we correctly assign an executable result from our stored procedure to a SQL variable.
Last modified on 2024-02-21