0

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
Mr.B
  • 397
  • 5
  • 16
  • 1
    Please, show all relevant code, all variable declarations specifically. Check `SELECT @Result1, @Result2`. – Serg Jun 25 '19 at 13:20
  • apologies updated as above, let me know if i can clarify anything. – Mr.B Jun 25 '19 at 13:28
  • Possible duplicate of [How to assign an exec result to a sql variable?](https://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable) – iainc Jun 25 '19 at 13:35

2 Answers2

3

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.

Serg
  • 22,285
  • 5
  • 21
  • 48
1

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

Wesley Nightingale
  • 73
  • 1
  • 12