Dynamic "in" list for SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic "in" list for SP

Author

Topic

mj
Starting Member

25 Posts

Posted - 2002-07-10 : 14:45:17

I have an SP in which a region is passed,

depending on the region passed, I want to be able to have a list of states in that region built in the where clause, like so

Select [fields] from [table]

Where borrowerstate in

@RegionName =
case
when @Region = 'CMW' then ('MO' , 'IL' , 'IN' , 'OH' , 'MI')
end

I get an error by the @RegionName variable. Am I missing something here, I thought the syntax was correct. Will someone please point me in the right direction

Thanks a million

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-10 : 14:51:39

You'd need to use dynamic SQL to have this kind of functionality:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

You could also do it with a lot of IF...ELSE blocks, but you cannot do it using CASE, because CASE only returns an expression. The IN list requires a list of expressions or values.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-10 : 14:53:30

There are a few errors here.

First, @RegionName doesn't belong there at all. If you want to assign @RegionName a value you must do so in the column list of the SELECT.

Second, you can't put a CASE statement in a WHERE clause. You'll need to pull that logic out into IF statements, either before each SELECT or integrated into the WHERE clause like the following:


if @Region = 'CMW'
  select [fields] 
  from [table] 
  where borrowerstate in ('MO' , 'IL' , 'IN' , 'OH' , 'MI')

or

  select [fields] 
  from [table] 
  where @region = 'CMW' and borrowerstate in ('MO' , 'IL' , 'IN' , 'OH' , 'MI')

Jonathan Boott, MCDBA

mj
Starting Member

25 Posts

Posted - 2002-07-10 : 15:00:55

or...
You can realize that there is a field in the DB that contains the region as well, and not waste anyones time

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-07-10 : 16:52:17

Here is a stripped down version of a "no dynamic sql" approach to the problem that I pieced together a little while ago (with the help of sqlteam.com and graz's sql for array parsing at http://www.sqlteam.com/downloads/sp_parsearray.sql !!).

Super fast it is _not_, but that seems to be the inefficiency of using IN clause on large tables. Requires SQL 2000 for table variable support. Suggestions for improvement would be most welcome. The design had to work even when SELECT privileges are removed from the base table.

regards,

Dave Kawliche
http://AccessHelp.net
http://1ClickDB.com

CREATE PROCEDURE [dbo].[AssociationList]

@IDList varchar(2000)

AS

set arithignore on
set arithabort off
set ansi_warnings off
set nocount on

declare @IDListPosition int
declare @ArrValue varchar(2000)
--must declare correct data type for IDs
declare @TableVar table ( Assoc_Nbr varchar(50) NOT NULL )
set @IDList = COALESCE(@IDList ,'')
IF @IDList <> ''
BEGIN
--add comma to end of list so user doesn't have to
set @IDList = @IDList + ','
-- Loop through the comma demlimted string list
while patindex('%,%' , @IDList ) <> 0
begin
select @IDListPosition = patindex('%,%' , @IDList)
select @ArrValue = left(@IDList, @IDListPosition - 1)
-- Insert parsed ID into TableVar for WHERE IN select
Insert Into @TableVar (Assoc_Nbr) Values(@ArrValue)
-- Remove processed string
select @IDList = stuff(@IDList, 1, @IDListPosition, '')
END
END

SELECT

*

FROM

[Association]

WHERE
(
[Assoc_Nbr] IN (select Assoc_Nbr from @TableVar)
)

ORDER BY [Assoc_Nbr] ASC;

Edited by - Dave Kawliche on 07/10/2002 18:13:42

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-10 : 17:11:12

umm interesting Dave.. Although theres something much simpler. Like Robvolk said.. use dynamic sql

something along the lines of..

set @sql = 'Select [fields] from [table] Where borrowerstate in'+ case
when @Region = 'CMW' then '('''MO''' , '''IL''' , '''IN''' , '''OH''' , '''MI''')'
end
exec(@sql)

Hmm, I might have got the single quotes there wrong but it does look kinda right.

-----------------------
Take my advice, I dare ya

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-07-10 : 17:46:08

I dunno, its seems pretty straighforward to me ;-> Dynamic SQL was not an option for this project which had to conform to very strict system security policies.

djk

regards,

Dave Kawliche
http://AccessHelp.net
http://1ClickDB.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-11 : 16:04:20

Hmmm... Yes, Dynamic SQL requires SELECT permissions on the underlying tables, and that can be a concern in some security models. But, why wouldn't you create this Association table as a real table in your database instead of building it into a Table Variable every time? At least that should work for the initial problrem with states/regions.

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-07-11 : 17:19:16

AjarnMark -

Association is the base table data is being selected from. @IDList is the dynamically created (by the calling app) comma delimited list of IDs to return. That comma delimited @IDlist is parsed and inserted as separate records into the @TableVar so they can be used for the IN clause without dynamic sql.

I thought since @TableVar is in memory just for life of proc and doesn't write to disk (?) that it would be best choice. The posted code is abbreviated so maybe the full intent is not so clear. Further questions, suggestions, endorsements, or criticims of this technique are very welcome.

djk

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-07-11 : 18:06:29

AjarnMark + mj -

You made me realize the proc I posted doesn't really solve the original question...I thought I could just bang it out now but it is too late in the day now...maybe tomorrow

all my proc really shows is using dynamic IN clause without dynamic sql ... its a start

djk

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:13:22

Do you really require IN? I think that clause is the problem...

WHERE RegionName = (CASE WHEN @Region = 'CMW' THEN 'MO'
WHEN @Region = 'AAA' THEN 'A1' END)
WHEN @Region = 'ZZZ' THEN 'Z1' END)
OR RegionName = (CASE WHEN @Region = 'CMW' THEN 'IL'
WHEN @Region = 'AAA' THEN 'A2' END)
WHEN @Region = 'ZZZ' THEN 'Z2' END)
OR RegionName = (CASE WHEN @Region = 'CMW' THEN 'IN'
WHEN @Region = 'AAA' THEN 'A3' END)
WHEN @Region = 'ZZZ' THEN 'Z3' END)

and so on for each variation. It a bit hacky but might allow your project to progress!!!

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:14:59

... some syntax tidying ')' might help a bit!!!

Daniel Small MIAP
www.danielsmall.com IT Factoring

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-12 : 08:16:11

I didn't read this thread too carefully, but can you use the charindex function? It would allow you to avoid the dynamic SQL, at least....

<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-12 : 08:20:01

Probably not, because CharIndex would treat @IDList as a single expression instead of a list of expressions.