Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage
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'
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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?
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..
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..
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.
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.
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
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
File attached. Thanks
cpsp.xlsx
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)
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)
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?
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.
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.
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.
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.
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.
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)
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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'