CODEWITHSUNDEEP
sqlsql-server-2008
Mr.B
Mr.B

Reputation: 397

Assign SQL SP Result to Variables and Sum Result

Issue: both stored procedures return a int value but the sum of both int values returns the default 0

I've seen various solutions with selects which works or to use a temp table no problem but I need to keep the results separately and then sum them at the end.(allowing the sp's to be modular) any ideas keeping 2008 compatibility?

The main idea is to have a parent sp with a total result and several modular child sp's each being its own result that will be used by parent.

How to assign an exec result to a sql variable?

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

set @Total = @Result1 + @Result2

select @Total

Stored Proc - both are identical just use diff tables ( returned values are 1, 15)

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
declare @NullTotal int = 0;

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

select @NullTotal

Upvotes: 0

Views: 152

Answers (2)

Wesley Nightingale
Wesley Nightingale

Reputation: 73

There is a bug in the code

declare @Result1 int = 0,
@Result1 int = 0,
@Total int = 0,
@projectID int,
@periodID int

@Result1 is declared twice

Upvotes: 1

Serg
Serg

Reputation: 22811

There is no RETURN statement in procs, default return value is 0. Use RETURN @NullTotal instead of select @NullTotal to return an int value. SELECT in a proc returns result set (containig 1 row, 1 column in this case), not a scalar.

Upvotes: 3

Related Questions