Avatar of biotec

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

select  @datefield = (CASE WHEN (datefield is null or datefield = '') then '2'
ELSE '1'
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

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon