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

biotecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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'
0
biotecAuthor 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?
0
Pawan KumarDatabase ExpertCommented:
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..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
biotecAuthor 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
0
Pawan KumarDatabase ExpertCommented:
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)
0
Pawan KumarDatabase ExpertCommented:
edited my last comment. Please refresh the page.
0
biotecAuthor Commented:
Does this account for blanks?
0
Pawan KumarDatabase ExpertCommented:
Yes it will.
0
biotecAuthor 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?
0
Pawan KumarDatabase ExpertCommented:
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.
0
biotecAuthor 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.
0
Scott PletcherSenior DBACommented:
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.
0
Scott PletcherSenior DBACommented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.