Link to home
Start Free TrialLog in
Avatar of biotec
biotec

asked on

call stored procedure with multiple parameters

Even though I have only two print statements it still returns all the output parameters but returns all 0's. When I run the select statement in the SP I get values other than 0 returned. The SP is shown below.
declare @txt_dairy_nmbr	INT
declare @txt_fats_nmbr		INT   
declare @txt_fruit_nmbr		INT
declare @txt_grain_nmbr		INT
declare @txt_protein_nmbr	INT
declare @txt_vegetable_nmbr	INT
exec SVCHC_cpsp_dietary_totals '4F3A3CD5-832B-46AA-9D5B-87165798767A', '1', 'Initial' 
@txt_dairy_nmbr OUTPUT,
@txt_fats_nmbr	OUTPUT,   
@txt_fruit_nmbr	OUTPUT,
@txt_grain_nmbr	OUTPUT,
@txt_protein_nmbr OUTPUT,
@txt_vegetable_nmbr OUTPUT
print @txt_dairy_nmbr
print  @txt_fats_nmbr

Open in new window


Stored Procedure:
CREATE PROCEDURE [dbo].[SVCHC_cpsp_dietary_totals]
(
--@Design Bit,
@pi_cur_person_id  UNIQUEIDENTIFIER,
@pregnum			INT,
@txt_visit			VARCHAR (15),
@txt_dairy_nmbr		INT	OUTPUT, 
@txt_fats_nmbr		INT	OUTPUT,   
@txt_fruit_nmbr		INT	OUTPUT,
@txt_grain_nmbr		INT	OUTPUT,
@txt_protein_nmbr	INT	OUTPUT,
@txt_vegetable_nmbr	INT	OUTPUT
)
AS
 select 
sum(ISNULL(@txt_dairy_nmbr,'')) as txt_dairy_nmbr,
sum(ISNULL(@txt_fats_nmbr,'')) as txt_fats_nmbr,
sum(ISNULL(@txt_fruit_nmbr,'')) as txt_fruit_nmbr,
sum(ISNULL(@txt_grain_nmbr,'')) as txt_grain_nmbr,
sum(ISNULL(@txt_protein_nmbr,'')) as txt_protein_nmbr,
sum(ISNULL(@txt_vegetable_nmbr,'')) as txt_vegetable_nmbr
FROM SVCHC_CPSP_Dietary_ext_
where person_id =  @pi_cur_person_id 
and pregNum = @pregnum
and txt_visit = @txt_visit

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

try calling it like

exec dbo.SVCHC_cpsp_dietary_totals '4F3A3CD5-832B-46AA-9D5B-87165798767A', '1', 'Initial', @txt_dairy_nmbr, @txt_fats_nmbr, @txt_fruit_nmbr, @txt_grain_nmbr, @txt_protein_nmbr, @txt_vegetable_nmbr
print @txt_dairy_nmbr;
print @txt_fats_nmbr;

Open in new window

I guess this is what you are trying to do

ALTER PROCEDURE [dbo].[SVCHC_cpsp_dietary_totals]
(
--@Design Bit,
@pi_cur_person_id  UNIQUEIDENTIFIER,
@pregnum			INT,
@txt_visit			VARCHAR (15),
@txt_dairy_nmbr		INT	OUTPUT, 
@txt_fats_nmbr		INT	OUTPUT,   
@txt_fruit_nmbr		INT	OUTPUT,
@txt_grain_nmbr		INT	OUTPUT,
@txt_protein_nmbr	INT	OUTPUT,
@txt_vegetable_nmbr	INT	OUTPUT
)
AS
begin
set nocount on;
select @txt_dairy_nmbr = 1, @txt_fats_nmbr = 2, @txt_fruit_nmbr = 3, @txt_grain_nmbr = 4, @txt_protein_nmbr = 5, @txt_vegetable_nmbr = 6;
end;

Open in new window


declare @txt_dairy_nmbr	INT
declare @txt_fats_nmbr		INT   
declare @txt_fruit_nmbr		INT
declare @txt_grain_nmbr		INT
declare @txt_protein_nmbr	INT
declare @txt_vegetable_nmbr	INT
exec dbo.SVCHC_cpsp_dietary_totals '4F3A3CD5-832B-46AA-9D5B-87165798767A', '1', 'Initial', @txt_dairy_nmbr output, @txt_fats_nmbr output, @txt_fruit_nmbr output, @txt_grain_nmbr output, @txt_protein_nmbr output, @txt_vegetable_nmbr output
print @txt_dairy_nmbr;
print @txt_fats_nmbr;

1
2

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of biotec
biotec

ASKER

I'm not sure why you set the output variables to static integers? These output variables will not be static numbers they will be the sum of columns in a table based on the input parameters passed in. They will be 4 for one and 5 for another and possible 0 (that is why I used the isnull on all these so we get back a blank or 0).
When I run the select statement in the SP by itself it works great. The select below returns
 select 
sum(ISNULL(txt_dairy_nmbr,'')) as dairy_nmbr,
sum(ISNULL(txt_fats_nmbr,'')) as fats_nmbr,
sum(ISNULL(txt_fruit_nmbr,'')) as fruit_nmbr,
sum(ISNULL(txt_grain_nmbr,'')) as grain_nmbr,
sum(ISNULL(txt_protein_nmbr,'')) as protein_nmbr,
sum(ISNULL(txt_vegetable_nmbr,'')) as vegetable_nmbr
FROM SVCHC_CPSP_Dietary_ext_

Open in new window


User generated image
Avatar of biotec

ASKER

Ok, your method worked other than the null returned which is what I was trying to avoid with my isnull. I guess I shouldn't have been doing as part and the isnull in the stored procedure?
sum(ISNULL(@txt_dairy_nmbr,'')) as txt_dairy_nmbr

I guess the question is also how to get rid of the Null and return a 0?

Warning: Null value is eliminated by an aggregate or other SET operation.
4
2
I'm not sure why you set the output variables to static integers?

that is demo to show how to pass output
how to call
how to use return values...

in your case, I posted the solution above, ID: 42334297
I guess the question is also how to get rid of the Null and return a 0?

@txt_fats_nmbr = sum(txt_fats_nmbr),

>>>

@txt_fats_nmbr = isnull(sum(txt_fats_nmbr),0),
Avatar of biotec

ASKER

Thank you, very close now. The only odd part is that the numbers are correct but they are in the wrong order for the print statements.
declare @txt_dairy_nmbr	INT
declare @txt_fats_nmbr		INT   
declare @txt_fruit_nmbr		INT
declare @txt_grain_nmbr		INT
declare @txt_protein_nmbr	INT
declare @txt_vegetable_nmbr	INT
exec dbo.SVCHC_cpsp_dietary_totals '4F3A3CD5-832B-46AA-9D5B-87165798767A', '1', 'Initial', @txt_dairy_nmbr output, @txt_fats_nmbr output, @txt_fruit_nmbr output, @txt_grain_nmbr output, @txt_protein_nmbr output, @txt_vegetable_nmbr output
print @txt_dairy_nmbr;
print @txt_fats_nmbr;
print @txt_fruit_nmbr		
print @txt_grain_nmbr		
print @txt_protein_nmbr	
print @txt_vegetable_nmbr	

Open in new window


The output is shown below but should look like the pic attachment I included in the previous thread.

Warning: Null value is eliminated by an aggregate or other SET operation.
4
2
0
4
2
2
what are you trying to do here?
Whats the purpose of  printing?

use this to see what is what

print 'dairy_nmbr : ' + cast(@txt_dairy_nmbr as varchar);

Open in new window

The only odd part is that the numbers are correct but they are in the wrong order for the print statements.

check your code
I dont think order is wrong :)
Avatar of biotec

ASKER

I thought Print was the best way to see the output for the stored procedure? Is there a better way to do it? Doing it with your suggestion returns the wrong sums for the columns.
Warning: Null value is eliminated by an aggregate or other SET operation.
dairy_nmbr : 4
fats_nmbr : 2
fruit_nmbr : 0
grain_nmbr : 4
protein_nmbr : 2
vegetable_nmbr :2
User generated image
to get rid of that warning add this to your sp

SET ANSI_WARNINGS OFF

Open in new window


whats is wrong with sums?
what are the values in your table?

select * from SVCHC_CPSP_Dietary_ext_

Open in new window

Avatar of biotec

ASKER

You are correct. Somehow I'm not sure how to do a select statement that also does a sum on the columns. When I just select the columns it does come out with the numbers that match the called stored procedure. I just can't seem to get the syntax right for running a basic select statement that also sums the columns properly and in the right order.
Avatar of biotec

ASKER

Thank you and if you can provide an example of how to properly do a basic select statement that includes the "sum" of the columns as well that would be great. Otherwise, greatly appreciate all your help. :)
basic select and sum cannot be made on same sql...
you can add 2 select into your sp

select * from yourtable where ....;
select sum(..) sum1, sum(...) sum2,... form yourtable where ....;

Open in new window