"IN" statement failing when multiple items passed in 3

[glgcag]

I am dynamically creating a WHERE clause in my query, and the the "in" statement is only processing the first of the multiple values I pass it. Here's the statement:

sSQL = sSQL + 'WHERE tAGProjects.AGProjectKey IN (COALESCE(' + @AGProjectKey + ', tAGProjects.AGProjectKey))'

I pass into the stored proc @AGProjectKey='5998,10779' and it only returns the record with the key of 5998. Why isn't it processing both keys I'm passing it?

 

[glgcag]

Oops, mixed table and parameter names, sorry, reposting with the proper names:

I'm a little fuzzy here- I get that if the column isn't allowed to be null, I'm fine as is. But what I'm trying to understand is if the parameter tests as null, I want to essentially ignore the criteria in the where clause identified with that parameter. So if @AGProjectKey IS NULL, do not use the tAGProjects.AGProjectKey column as a criteria. What I thought did that was this line:

ISNULL(tAGProjects.AGProjectKey, 0) IN(CASE WHEN @AGProjectKey IS NULL THEN ISNULL(tAGProjects.AGProjectKey, 0) ELSE @AGProjectKey END)

This line seems to me to mean that if the tAGProjects.AGProjectKey Is Null, make it = 0. Then if @AGProjectKey is null, make tAGProjects.AGProjectKey = tAGProjects.AGProjectKey (including converted nulls to 0) so that it returns all records, but if @AGProjectKey isn't null, then tAGProjects.AGProjectKey must equal what is in the parameter and return only those records. This covers ALL bases, but as you see in my earlier comment above, I can't insert the SELECT value FROM dbo.fn_Split in the CASE statement without erroring out.

 

[ESquared]

ISNULL(tAGProjects.AGProjectKey, 0)

This is the expression we're pointing to. You would only be converting this to 0 when it is null, if it can possibly be null and you want to be able to select those that are null by passing in '0' in @AGProjectKey.

The part about selecting all records of @AgProjectKey is NULL was already handled by both George's and my code.

If the AGProjectKey column cannot be null, then all you have to do is:

WHERE
   @AGProjectKey IS NULL
   OR tAGProjects.AGProjectKey IN (SELECT Value FROM dbo.fn_Split(@AGProjectKey,','))

or

IF @AGProjectKey IS NULL SET @AGProjectKey = '-1'

JOIN dbo.fn_Split(@AGProjectKey, ',') K ON tAGProjects.AGProjectKey = K.Value OR @AGProjectKey = '-1'

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]