March 4, 2011 at 8:58 am
I can’t believe I cannot find this answer anywhere... I think the word "IN" is confusing all the search engines.
I have a stored procedure that I need to pass several user selected values into. I need to use the IN operator in my where clause to pull out the data. Example:
Select * FROM Vehicles Where Veh IN ('Ford','Acure','GM')
I'm sure this is a common senario...
Any help would be great, Thank you.
March 4, 2011 at 9:05 am
That will work as posted, but I bet what you're running into is passing a comma-delimited list as a single parameter and doing this kind of thing:
create proc MyProc
(@MyList varchar(100))
as
set nocount on;
select *
from MyTable
where Col1 in (@MyList);
If that's the kind of thing you're doing, you either need to parse out the list using a string parser, or you need to run the final query as dynamic SQL.
Even better, if you're using SQL 2008 (based on the forum you posted in), change the parameter to a table variable, put the list in that, and use that in the query instead.
Is that the case here?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2011 at 9:05 am
This is very common scenario. You have a couple choices. You can either run dynamic sql or parse the values. Given the pretty common appearance of your string you might check out this function from Jeff Moden DelimitedSplit8k.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2011 at 9:38 am
"change the parameter to a table variable" - Brilliant!!! 😀
Thanks!
March 4, 2011 at 9:48 am
stewsterl 80804 (3/4/2011)
"change the parameter to a table variable" - Brilliant!!! 😀Thanks!
If you get the chance, please, I'd love to see the code that you end up with for that. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2011 at 10:55 am
Jeff Moden (3/4/2011)
stewsterl 80804 (3/4/2011)
"change the parameter to a table variable" - Brilliant!!! 😀Thanks!
If you get the chance, please, I'd love to see the code that you end up with for that. Thanks.
Here's a proof-of-concept you can run if you have access to 2008 Dev or Express or whatever:
create type dbo.InputTable as table
(ID int identity primary key,
Col1 varchar(100));
go
create proc dbo.TestInputTable
(@Input InputTable readonly)
as
set nocount on;
select *
from @Input;
go
declare @Outer InputTable;
insert into @Outer (Col1)
values ('A'),('B');
exec dbo.TestInputTable @Outer;
The real advantage it comes in when you tie a .NET dataset into the parameter. With that, devs can take things like multi-select controls and pass them to non-dynamic SQL in one go.
In the proc, you just do a join to the parameter as if it were any other table variable, or otherwise do with it what you will. Except modify its contents. If you need to do that, you need to move it into either a temp table or another @table, since the parameter has to be read-only.
I've used this model in production code, and it makes it very easy on the web devs, and performs nicely, debugs easily, et al.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2011 at 11:37 am
Cool. Thanks, Gus. I'll give it a try and read up on it in BOL but simple examples like the one you provided go a very long way in making things simple to understand.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2011 at 11:57 am
Jeff Moden (3/4/2011)
Cool. Thanks, Gus. I'll give it a try and read up on it in BOL but simple examples like the one you provided go a very long way in making things simple to understand.
Exactly. Which is why I wrote it.
I also just remembered that someone asked me to write an article about using them a while back. I'll have to get that done!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2011 at 12:44 pm
Gus, that may have just revolutionized how I handle those types of situations from this point on. I took your example and put together a quick proof of concept aspx page. BRILLIANT!!!! I just add a parameter to the stored proc call and in the .net code the parameter is a DataTable. WOW!!! I see why your guys dig this. I could see making a type for a few datatypes (int, (n)varchar) at the very least.
Thanks for making me lose a good chunk of afternoon productivity. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2011 at 3:26 pm
Sean Lange (3/4/2011)
Gus, that may have just revolutionized how I handle those types of situations from this point on. I took your example and put together a quick proof of concept aspx page. BRILLIANT!!!! I just add a parameter to the stored proc call and in the .net code the parameter is a DataTable. WOW!!! I see why your guys dig this. I could see making a type for a few datatypes (int, (n)varchar) at the very least.Thanks for making me lose a good chunk of afternoon productivity. 😛
Sean... can you post your proof of concept aspx page? It would make the example complete.
I'm not a GUI developer and couldn't write ASP to save my life. I think this concept would be a good one to write an "SQL Spackle" article on. Short, sweet, and to the point! 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2011 at 8:22 am
Jeff - I will try to get it cleaned up a little bit and post it either today or tomorrow.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 8:54 am
Sean Lange (3/4/2011)
Gus, that may have just revolutionized how I handle those types of situations from this point on. I took your example and put together a quick proof of concept aspx page. BRILLIANT!!!! I just add a parameter to the stored proc call and in the .net code the parameter is a DataTable. WOW!!! I see why your guys dig this. I could see making a type for a few datatypes (int, (n)varchar) at the very least.Thanks for making me lose a good chunk of afternoon productivity. 😛
Isn't that what Friday afternoons are for?
It's a pretty cool feature, that's for sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 7, 2011 at 9:29 am
Isn't that what Friday afternoons are for?
Yes, but I try my best not to limit to just Friday's.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 9:36 am
Jeff - here is my proof of concept. I spent a little time to clean it up so that somebody else might be able to understand.
The sql file in there creates the IntTable type, a table of fakenames for use in the output, a stored proc to get our data, and an insert to fill the fakenames table.
I built this as a website so it should work anywhere under your IIS. You will have to setup the folder as an application in IIS. You will also need to modify the connection string in the web.config to whatever yours would look like.
Hope this helps. Let me know if you have any problems.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 8, 2011 at 6:29 pm
Sean Lange (3/7/2011)
Jeff - here is my proof of concept. I spent a little time to clean it up so that somebody else might be able to understand.The sql file in there creates the IntTable type, a table of fakenames for use in the output, a stored proc to get our data, and an insert to fill the fakenames table.
I built this as a website so it should work anywhere under your IIS. You will have to setup the folder as an application in IIS. You will also need to modify the connection string in the web.config to whatever yours would look like.
Hope this helps. Let me know if you have any problems.
Thanks, Sean. As a side bar, this has the makings of a good article. You and Gus should get together and write one. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply