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!
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'
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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'
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'
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial