asked on

using case in basic stored procedure to account for nulls or blank not working quite right

This is a basic sp but it returns a 1 properly but will not return anything when the statement is true. I'm sure my null trapping is not correct. Also not sure I dealt with the output parameter correctly in terms of how I set it equal to the case statement.

@pi_enterprise_id  CHAR(5),
@pi_practice_id  CHAR(4),
@txt_pregnum            INT,
@problem                  VARCHAR (150),
@pi_cur_person_id  UNIQUEIDENTIFIER,
@datefield            VARCHAR(10) OUTPUT
)
AS

select  @datefield = (CASE WHEN (datefield is null or datefield = '') then '2'
ELSE '1'
END)
from ob_problem_list_
where person_id = @pi_cur_person_id
and pregnum = @txt_pregnum
and problem = @problem 
and cpsp_problem_indicator = 'Y'

Open in new window

Pawan Kumar

It will not be NULL. It will be 1900/01/01

Please try updated code.-

@pi_enterprise_id CHAR(5),
@pi_practice_id CHAR(4),
@txt_pregnum INT,
@problem VARCHAR (150),
@pi_cur_person_id UNIQUEIDENTIFIER,
@datefield VARCHAR(10) OUTPUT
)
AS

select @datefield = CASE WHEN datefield = '1900/01/01' OR datefield is null or datefield = '' then '2' ELSE '1' END
from ob_problem_list_
where person_id = @pi_cur_person_id
and pregnum = @txt_pregnum
and problem = @problem
and cpsp_problem_indicator = 'Y'

biotec

ASKER

I'm not sure how it would get set to this, I realize it's a varchar which is unfortunately how this vendor does dates at times, but generally when I query for this column it either has a NULL or '' or YYYYMMDD?

Pawan Kumar

This ???

select TOP 1 @datefield = CASE WHEN datefield = '19000101' OR datefield is null or datefield = '' then '2' ELSE '1' END

can you show me some data you are getting..

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

biotec

ASKER

I'm also wondering what happens if the fact that querying this table does not return any NULL or '' values means that it won't show the false portion of the query. I'm not sure a row can be created without that date but I haven't thoroughly tested that assumption.

File attached. Thanks
cpsp.xlsx

SOLUTION

Pawan Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Pawan Kumar

edited my last comment. Please refresh the page.

biotec

ASKER

Does this account for blanks?

Pawan Kumar

Yes it will.

biotec

ASKER

Scott I think you are correct and I'm wondering if what your query will still return a blank or rather nothing if there are no rows?

Pawan Kumar

This will take care of the NULLs.

SET @datefield = ISNULL(@datefield,2)

If your query does not return anything then @datefield will be NULL , so after that we can just check for NULL and replace it with 2.

biotec

ASKER

Thanks guys. Both ideas were helpful so shared the points. Appreciate it. I just wasn't accounting for null or the non-existence of anything being returned.

Scott Pletcher

Yes, the code I provided:

set @datefield = case when exists(
select 1
from ob_problem_list_
where person_id = @pi_cur_person_id
and pregnum = @txt_pregnum
and problem = @problem
and cpsp_problem_indicator = 'Y'
) then '1' ELSE '2' END

will set the return value whether a row is found or not. But changing the data type and/or using INULL() around the column or anything like that will not make any different at all, you still won't get a value back because SQL didn't return a row to begin with.

Scott Pletcher

If your query does not return anything then @datefield will be NULL

False! (unless @datefield just happened to be NULL prior to running the proc)