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.
Stored Procedure:
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
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
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;
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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_
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
sum(ISNULL(@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),
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.
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
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
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
Whats the purpose of printing?
use this to see what is what
print 'dairy_nmbr : ' + cast(@txt_dairy_nmbr as varchar);
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 :)
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
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
to get rid of that warning add this to your sp
whats is wrong with sums?
what are the values in your table?
SET ANSI_WARNINGS OFF
whats is wrong with sums?
what are the values in your table?
select * from SVCHC_CPSP_Dietary_ext_
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.
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
you can add 2 select into your sp
select * from yourtable where ....;
select sum(..) sum1, sum(...) sum2,... form yourtable where ....;
Open in new window