coalesce in stored procedure

Question

coalesce in stored procedure

greysun75

SSC Enthusiast

Points: 194
More actions
June 13, 2008 at 10:47 am

#187100

I am trying to do a dynamic where clause in a stored procedure using coalesce but I am having problems.
Below is the stored procedure. The problem is when ever I submit a blank value into one of the variables SQL does not return any results. Its like even though when I call the stored procedure and use an empty variable as a parameter it is not recognized as null by SQL so it doesnt move on to comparing the field name to itself. Any help here would be much appreciated.
ALTER PROCEDURE JSS_SELECT_SALARY_INFO
@StateID nvarchar(10),
@RegionID nvarchar(10),
@PositionID nvarchar(10)
AS
Begin
SELECT *
FROM V_JSS_ALL
WHERE KIS_JSS_STATE_NAME Is Not Null
AND STATE_ID = COALESCE(@StateID ,STATE_ID)
AND TYPE_ID = COALESCE(@PositionID,TYPE_ID)
AND REGION_ID = COALESCE(@RegionID ,REGION_ID)
End
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

A "blank" variable is not a NULL value therefore coalesce will return a zero-length string and your "ANDs" will be false.

* Noel

greysun75 SSC Enthusiast Points: 194 More actions · Answer 2

So how do I get around this if I am not sure which of the variables will contain values. The stored procedure is being called like this

Set rsResults = cn.Execute("JSS_SELECT__INFO '" & strStates & "','" & numRegion & "','" & numJT & "','" & strYears & "'")

but either strStates or numRegion will contain a value, not both

noeld SSC Guru Points: 96590 More actions · Answer 3

greysun75 (6/13/2008)
So how do I get around this if I am not sure which of the variables will contain values. The stored procedure is being called like this
Set rsResults = cn.Execute("JSS_SELECT__INFO '" & strStates & "','" & numRegion & "','" & numJT & "','" & strYears & "'")
but either strStates or numRegion will contain a value, not both

you could either check for that on the client and only conactenate the values that are not blank

or on the stored proc you could add, right at the beginning:

SELECT @StateID = NULLIF(@StateID,''),

@RegionID = NULLIF(@RegionID,''),

@PositionID = NULLIF(@PositionID,'')

* Noel

greysun75 SSC Enthusiast Points: 194 More actions · Answer 4

You are AWESOME 🙂

I have a ton of stuff being selected so instead of doing what you recommended in the select just before the select I did this

Set @StateID = NULLIF(@StateID,'')

Set @RegionID = NULLIF(@RegionID,'')

Set @PositionID = NULLIF(@PositionID,'')

Which seemed to work beautifully.

Is there anything wrong with doing it this way that you can think of?

The entire SP is below

ALTER PROCEDURE JSS_SELECT_SALARY_INFO

-- Add the parameters for the stored procedure here

@StateID nvarchar(10),

@RegionID nvarchar(10),

@PositionID nvarchar(10),

@DateSelected nvarchar(10)

AS

Begin

Set @StateID = NULLIF(@StateID,'')

Set @RegionID = NULLIF(@RegionID,'')

Set @PositionID = NULLIF(@PositionID,'')

SELECT *

FROM V_JSS_ALL

WHERE KIS_JSS_STATE_NAME Is Not Null

AND STATE_ID = COALESCE(@StateID ,STATE_ID)

AND TYPE_ID = COALESCE(@PositionID,TYPE_ID)

AND STATE_REGION_ID = COALESCE(@RegionID ,STATE_REGION_ID)

End

GO

noeld SSC Guru Points: 96590 More actions · Answer 5

There are problems.

1- you are using dynamic sql to call the stored procedure so you are open for sql injection.

2. If you use parameters and pass them as NULL you don't need that extra code but this is just

a matter of taste

3. This could potentially not use indexes very well but I have no idea how big/small are your tables.

In SQL there is always room for improvements 😉

* Noel