Can I have a dynamic 'where in' clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can I have a dynamic 'where in' clause?

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2012-10-29 : 06:38:59

I want to SELECT people from a table depending on what type of employment they are in. The filter options on the website using a listbox are FullTime, PartTime, Flexible, and All.

If a user leaves the All option selected in the listbox, then SQL should return all records. If a user selects 'FullTime' then it will return just full-time staff. If a user selects both 'FullTime' and 'PartTime' then it returns those who are working full-time AND part-time.

The way it works so far is:


DECLARE @WorkType varchar(50)
SET @WorkType = NULL

SELECT 
 Name, WorkType
FROM 
 Employees
WHERE
 WorkType = COALESCE(@WorkType, WorkType)

So what is happening is if the user selects 'All' on the website then nothing is passed to the @WorkType parameter so it stays as default NULL. By using COALESCE it will skip the parameter value because it is NULL, and check that Worktype = WorkType which is always true so all records are returned. But if a user selects a type of employment then @WorkType parameter is being set to 'FullTime' or 'PartTime' or 'Flexible' by the website and the COALESCE function will check against the parameter value.

The code works fine if only one option is selected, but how would I go about getting it accept a list of values to check against?

I was thinking it should be like this if someone selects 'FullTime' and 'PartTime' on the website:


DECLARE @WorkType varchar(50)
SET @WorkType = 'FullTime, PartTime'

SELECT 
 Name, WorkType
FROM 
 Employees
WHERE
 WorkType IN (COALESCE(@WorkType, WorkType))

But the above won't work.

Anyone know how to do this? It will prevent me having to write the SQL code within the website application itself using looping or something.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 07:01:43

The quick and easy way to do this is to change the where clause like shown below:

WHERE
	(','+@WorkType+',' LIKE '%,'+WorkType+',%')
	OR
	@WorkType IS NULL;

But this may not be great from a performance perspective on two accounts - bad query plans, and inability to use indexes. If you run into performance issues, please post back.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-29 : 07:56:02

even if you don't run into performance problems right now. Read this:

http://www.sommarskog.se/arrays-in-sql-2008.html

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

token
Posting Yak Master

133 Posts

Posted - 2012-10-29 : 08:24:56

ok table valued paramters it is then. The only issue is that I still have to write the values I want to search on into a table of TYPE table and then pass those into the stored procedure. It seems it will be take longer to do this than just write the SQL dynamically within the website application code?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 08:49:18

I am not a fan of TVP's (probably fear of the unknown). There are some other alternatives on another article that Sommarskog wrote - here http://www.sommarskog.se/arrays-in-sql-2005.html

My favorite, both for its simplicity and for ease of use from .Net code is passing arrays as XML. In your case, something like shown below:

DECLARE @WorkTypesXML XML;
SET @WorkTypesXML = 
'<WorkTypes>
	<WorkType>FullTime</WorkType>
	<WorkType>PartTime</WorkType>
</WorkTypes>';

SELECT 
 Name, WorkType
FROM 
 Employees e
 INNER JOIN 
 (
 	SELECT
		WorkType.value('.','varchar(255)') AS WorkType
	FROM
		@WorkTypesXML.nodes('//WorkType') WorkTypes(WorkType)
 )w ON w.WorkType = e.WorkType

token
Posting Yak Master

133 Posts

Posted - 2012-10-29 : 12:44:51

Thank you sunitabeck. I have tried the TvP and they are not good. You basically create a table with columns and data types and name it. Then in a SP you declare a variable and make its 'type' the name of the table you just created. Exactly as you would create a variable and give it a type of varchar.

Its not really much different from having a temp table in my opinion (but I am a novice user so what do I know) and it hasn't solved the dynamic where clause problem I have.

I will try this XML solution and hopefully it works! Surely almost any website with a search facility .e.g amazon, ebay would have the same issue I am having - how to allow users the ability to search on more than criteria within the same column.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-29 : 15:17:37

hi token.

quote:

Its not really much different from having a temp table in my opinion (but I am a novice user so what do I know) and it hasn't solved the dynamic where clause problem I have.

Exactly -- it should be almost identical to this...

Maybe you should show us the sql you are writing. When you pass the list of things in as a tvp then you should simply JOIN on the table to filter your values

Say you had a dropdown box which listed types of shoe and the user wanted to see things that were either "Boots", "Trainers" or "Slippers"

then you would pass in a tvp of type [ShoeTypes] (which would probably contain the KEY of that type for the Shoes table

And you would have sql that looked like


-- @ShoesFilter is a tvp

SELECT <Columns>
FROM
    ShoeStock AS ss
    JOIN @ShoesFilter AS sf ON sf.ShoeID = ss.ShoeID

Which makes the sql nice and neat.

Passing XML blobs around is horrible. If you have a properly constructed tvp type with a PRIMARY KEY (so you even get a clustered index on the column) performance should be good and the sql is *much* more declarative

quote:

I will try this XML solution and hopefully it works! Surely almost any website with a search facility .e.g amazon, ebay would have the same issue I am having - how to allow users the ability to search on more than criteria within the same column.

Don't think they use sql server.. There are lots of technologies where passing this kind of thing is much more native.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 16:25:53

quote:
Passing XML blobs around is horrible.

I have used XML for passing data between client and SQL and it has worked very well for small datasets, such as what seems to be in this case. It is flexible, easily generated in .Net code and easily shredded to relational tables.

If you want to pass around a few thousand rows of data that might be iffy, and if you have tens of thousands, it probably wouldn't be the best choice. But for small datasets, I find that the flexibility offered by XML makes it ideal.

token
Posting Yak Master

133 Posts

Posted - 2012-10-30 : 06:47:05

the thing is that I have only asked my question using WorkType as a filter example. In reality there are many other filters such as Gender, Ethnicity, PayLevel.

When I made my TvP table I gave it the columns of WorkType, Gender, Ethnicity and PayLevel. The complexity happens when I want to display all records using the website in which case the website has to write all possible combinations to the table variable before being used in the SP to retrive the records. This seems a bit unelegant?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 07:23:27

Creating all combinations does seem very inelegant and inefficient. Instead, you can create a separate TVP for each of the filter conditions and join to each of those TVP's. In cases where the user wants to get all combinations for a given filter, simply fill in all the values for that attribute - which presumably is only a few items.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-30 : 11:11:37

quote:
Originally posted by sunitabeck

quote:
Passing XML blobs around is horrible.
I have used XML for passing data between client and SQL and it has worked very well for small datasets, such as what seems to be in this case. It is flexible, easily generated in .Net code and easily shredded to relational tables.

I didn't say it didn't *work* :)

I just don't like it.... I find conceptually tvps are the *right* thing to do.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 11:26:39

You are probably right about TVPs, and probably not just conceptually, but also if you like to stick to relational database principles. Even though I use XML liberally, bringing XML into SQL databases sometimes feels like bringing a stranger into your home and letting him sleep on the couch.

The thing that nags me about TVPs is that I have to create a user defined type, which somehow makes me think as though I am polluting the environment with my own types. I want to have as little impact on the environment as I can and leave it pristine for future generations

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-30 : 11:58:44

yeah -- they certainly don't make it easy to do things the right way.

I've resorted to doing things like making a type that models a list of ints, a list of varchar's etc

And then reusing that type for similar tasks

It's not ideal but it works.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources