December 30, 2010 at 6:17 am
Hi All,
I have the following Stored Proc. If I pass a valid value for the @ABIClass variable, I receive valid results. If I pass the value 'NULL' I receive no results.
If I change my coalesce statement to (just to prove a point):
coalesce(NULL,ABIClass))..... I receive expected results.
Can someone tell me How I should be setting my @BuildStr variable prior to execution to ensure that Coalesce uses the @ABIClass set to NULL correctly.
ALTER procedure [dbo].[spCCOccupationsSearchExtended]
(
@UserRoleIDint,
@ABIClasschar(10),
@OccupationFiltervarchar(50)
)
as
declare@LocalErrorint,
@LocalRowCountint,
@posStart int,
@BuildStr varchar(max)
set @posStart = 1
set @BuildStr = ''
if len(@OccupationFilter) > 1
Begin
While @posStart > 0
Begin
set @BuildStr = @BuildStr + ' and Name like ''%' + substring(@OccupationFilter,1, case patindex('% %',@OccupationFilter) when 0 then len(@OccupationFilter) else patindex('% %',@OccupationFilter)-1 end) + '%'''
set @posStart = patindex('% %',@OccupationFilter)
set @OccupationFilter = substring(@OccupationFilter,patindex('% %',@OccupationFilter)+1,len(@OccupationFilter) - patindex('% %',@OccupationFilter)+1)
End
End
-- RETURN RESULTS
set @BuildStr = 'select CCOccupationID,Code,
Name,
ABIClass,
RiskClassID
fromCCOccupations
wherertrim(ABIClass)like ''%'' + rtrim(coalesce(' + @ABIClass + ', ABIClass)) + ''%''' + @BuildStr + ' order by DisplayOrder'
exec (@BuildStr)
-- Check for errors
select@LocalError= @@error
if not(@LocalError = 0)
begin
raiserror ('spCCOccupationsSearchExtended[1000]: Unable to return results for @Search [%s] (@LocalError [%d])', 16, 1, @LocalError)
return 1000-- Return indicating failure
end
return 0
December 30, 2010 at 6:29 am
It's because when you test for NULL, you shouldn't include quotation marks, otherwise you're testing for the specific value "NULL" rather than for a null value. You need to include logic to omit the '%%' part if @ABIClass is NULL.
John
December 30, 2010 at 6:35 am
as you know, the value null is not NULL !
declare @var varchar(128)
set @var = 'NULL'
if @var is null
begin
select @var, ' it is indeed null 1'
end
else
begin
select @var, ' it is NOT null 1'
end
Set @var =null
if @var is null
begin
select @var, ' it is indeed null 2'
end
else
begin
select @var, ' it is NOT null 2'
end
-- forgot the alter the last selects to 2.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply