"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?

 

[Lamprey13]

I'd suggest getting a Split/Parse fuction and losing the Dynamic Sql, but I think this will work:

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

 

[glgcag]

Lamprey13,

Thank you for the response. I'd LOVE to get rid of this whole sSQL = sSQL + . . . stuff! I thought I'd get rid of it once I left Access, but it feels like the only way to pass an "IN(XXX)" dynamically is by making the whole stored proc a string! It's horrible!

Do you pass a comma separated string of id's into the stored proc and then parse and process them in the stored proc instead of using the "IN" statement? Would you have an example of how you handle multiple values passed into a stored proc?

 

[mrdenny]

I agree, I'd ditch the dynamic SQL.

Create a table variable, parse the text string out (usually via a function) and load the values into the table variable.

Then use the table variable in the where.

...
where tAGProjects.AGProjectKey IN (select Key from @TableVar)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

faq183-3979
faq183-5207
faq183-6684



-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

In case you missed it, look what George posted: the exact thing you're looking for. You don't have to use dynamic SQL a tall.

[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]

 

[glgcag]

Thank you guys~ sorry to ask what it seems like many have asked for already. I'm looking at George's links right now and I'm going to get it wired. Thanks!

 

[gmmastros]

They may be my "links", but the last 2 "belong" to ESquared because he wrote them.



-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

That reminds me, George, I have to update part III with all the info from the thread where we discussed it...

[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]

 

[glgcag]

I'm reading this MSDN article, and I'm wondering if there's anything like the Split() function built into SQL Server 2005?

 

[mrdenny]

No there isn't.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[glgcag]

Hey guys, what you turned me onto here is AWESOME! It's working great!

I have a question though about a line in my WHERE statement that is giving me an error. Essentially, what I'm trying to do is check if the parameter passed into the sp, @AGProjectKey, is not null, then I run my split function on the parameter and use it in my where statement. Here's the line:

ISNULL(tAGProjects.AGProjectKey, 0) 
  IN(CASE WHEN @AGProjectKey IS NULL THEN 
  ISNULL(tAGProjects.AGProjectKey, 0) 
  ELSE (SELECT Value FROM dbo.fn_Split(@AGProjectKey,','))

It runs fine if I strip out the case statement- not sure what's going on here!
  END)

I'm getting the following error when I run this code:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

[mrdenny]

Try something like this.

where (@AgProjectKey is null)
  OR (@AgProjectKey is not null
      and tAGProjects.AGProjectKey in (select value from dbo.fn_Split(@AgProjectKey, ','))
     )

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

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

You might experiment making this a JOIN instead of an IN clause. IN expands to a series of OR statements, which if the optimizer doesn't convert to a JOIN (which you can't rely on happening, in fact I'm not even sure if it can happen) it will perform far far worse than an explicit JOIN.

Here's some logic. If @AGProjectKey is null, set it to '-1'. Then use it like so

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

-- refreshing... BAH mrdenny you beat me.

[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]

 

[glgcag]

Wow, you weren't kidding about it not being optimal! It took 9 seconds to run with the above code. I have a very long query (150 lines) prior to the WHERE statement, so though I thought about approaching it from a JOIN, it just seemed too daunting. But if you think it would speed it up, I've got to do something!

 

[mrdenny]

Loose the Isnull if possible. Having the Isnull converts the query to a scan, basically making the index on the column useless.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

It doesn't matter how long the query is. Just insert the join line right after the tAGProjects table.

And what mrdenny said about index is good. If NULL means something, it shouldn't be NULL. NULL means "I don't know." Even with things as is, you might try:

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

[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]

 

[glgcag]

OK, getting rid of the ISNULL completely eliminated the delay. My problem is that the parameters for this stored proc are optional and when I declare them in the sp I'm setting them to null so that in the WHERE clause I can test for the existence of the criteria. Should I approach testing for a non-null value in the parameter a different way?

 

[ESquared]

We both already covered you for the optional parameters. Mrdenny's and my code all handle that. The question is about the AGProjectKey column in your tAGProjects table. Can it be NULL? If so, are you allowing selection of it with '0' in the input parameter? I assumed that because that's how your code was working that I looked at.

[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]

 

[glgcag]

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 tClients.ClientKey Is Null, make it = 0. Then if @ClientKey is null, make tClients.ClientKey = tClients.ClientKey (including converted nulls to 0) so that it returns all records, but if @ClientKey isn't null, then tClients.ClientKey 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.