another about null column question
-
I have an simple stored proc:
declare
@such nvarchar(52)
set
@such = '%' + @Suche + '%'
SELECT
ResponsibleNI
,
isnull
(ResponsibleBZ1, ' ') as ResponsibleBZ1,
isnull (ResponsibleBZ2, '1') as ResponsibleBZ2FROM CCon_Responsible
WHERE
ResponsibleBZ1
+ ResponsibleBZ2 like @such and
ResponsibleLKZ = 0
And the resultset didnt' contain a row where the ResponsibleBZ2 column ist null.
-
I'm not real sure what you are trying to do here, but your query will not return any rows if either ResponsibleBZ1 or ResponsibleBZ2 are NULL. A NULL value + any other value is NULL. You will need to use ISNULL or COALESCE in your where clause as well.
-
Yes that's the problem: If any ResponsibleBZ1 or ResponsibleBZ2 are NULL i get no row. How can I use ISNULL or COALESCE in the where clause?
-
Basically, just like you did in the SELECT clause.
declare @such nvarchar(52)
set @such = '%' + @Suche + '%'
SELECT
ResponsibleNI,
isnull (ResponsibleBZ1, ' ') as ResponsibleBZ1,
isnull (ResponsibleBZ2, '1') as ResponsibleBZ2FROM CCon_Responsible
WHERE
COALESCE(ResponsibleBZ1,'') + COALESCE(ResponsibleBZ2,'') like @such and
ResponsibleLKZ = 0
-
Cool
Thx a lot, John!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply