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

biotec
biotec used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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'

Author

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
The issue is that nothing will be returned if no row is found; not NULL, not 19000101, no value at all is returned (*).  I believe the variable will be unaffected at all since no row(s) is(were) returned.

This code should set the variable: adjust if/as needed to get the values you want when a row is not found:

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


(*) For example, run this code in any db:
declare @object_id int
select  @object_id = object_id from sys.objects where name = 'qea;ahoi8[p';
You'll see that nothing comes back from the query.

Author

Commented:
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
You need to use dateFieldText column , you are using.,.dateField

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 dateFieldText is null or dateFieldText= '' 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'

Also no need of CASE AND EXISTS simply use below..

SET @datefield  = ISNULL(@datefield,2)
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
edited my last comment. Please refresh the page.

Author

Commented:
Does this account for blanks?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Yes it will.

Author

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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.

Author

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial