stored procedure not returning a value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure not returning a value

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2015-01-10 : 06:25:40

If I pass in the value of 1, the stored procedure should return the value of (.5360268), else it should return 0 for any other value(null/blank/empty/etc)

The following stored procedure is returning the value 0 if I pass in the value of 1 which is wrong. And I would need to set to 0 for any other value. I am checking only for null or empty, but the if condition should check any value (except 1) and return 0 for it.

Could anyone please help with it.

ALTER PROCEDURE [dbo].[Calculator]
(
@ExtraCardiacArteriopathy bit
)

AS
BEGIN
declare @zarterio decimal(14,10)
declare @Result decimal(14,2)

IF @ExtraCardiacArteriopathy IS NULL OR @ExtraCardiacArteriopathy = ''
SET @ExtraCardiacArteriopathy = 0

if(@ExtraCardiacArteriopathy = 1)
begin
set @zarterio = .5360268
end
else
begin

set @zarterio = 0
end

set @Result = @zarterio

RETURN @Result
END

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-01-10 : 07:23:44

You have to declare the output field. Something like:

quote:
Originally posted by gagani
ALTER PROCEDURE [dbo].[Calculator]
(
@ExtraCardiacArteriopathy bit
,@Result decimal(14,2) output
)

AS
BEGIN
declare @zarterio decimal(14,10)
~~declare @Result decimal(14,2)~~

IF @ExtraCardiacArteriopathy IS NULL OR @ExtraCardiacArteriopathy = ''
SET @ExtraCardiacArteriopathy = 0

if(@ExtraCardiacArteriopathy = 1)
begin
set @zarterio = .5360268
end
else
begin

set @zarterio = 0
end

set @Result = @zarterio

RETURN ~~@Result~~
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-10 : 08:13:55

How are you calling the proc?

gagani
Posting Yak Master

112 Posts

Posted - 2015-01-10 : 14:10:22

I have made the changes as suggested above. When I tried to execute the procedure with input parameter as 78 or any other number, it is returing the value as .54. It should return 0.54 only for the input parameter as 1 but not for any other number/blank/null, etc.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-10 : 14:29:37

Your input parameter is a bit. If you pass it another number, it will be implicitly converted to a bit, e.g.

select cast(78 as bit)

returns 1, which in your proc produces a result of 0.54.

gagani
Posting Yak Master

112 Posts

Posted - 2015-01-10 : 18:29:02

thank you for the reply. Is the following right way of checking for a blank?

ltrim(rtrim(@LVEjectionFraction)) = ''

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-11 : 08:22:21

You don't need the ltrim rtrim. SQL considers any sequence of spaces as an empty string.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources