?
Solved

How do I use a varchar variable in an IN clause?

Posted on 2005-03-13
8
Medium Priority
?
301 Views
Last Modified: 2011-10-03
Hi,

I want to do this in a stored procedure...

SELECT * FROM qheader WHERE Category IN(@Categories)

where @Categories is a parameter passed from an aspx web page of the format 'Bikes','Cars','Mountains'.

But when I run the stored procedure no results are returned when I run it manually through Query Analyzer without using the @Categories variable it works fine.

I guess it has something to do with the quotations but I've tried lots of different combinations and formats and can't get it to work.

Any ideas?
0
Comment
Question by:nathc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 33

Expert Comment

by:sajuks
ID: 13527790
have you defined length for the input paramter @Categories ?/
for ex :create procedure usp_myproc( @categories varchar(10))
0
 
LVL 6

Assisted Solution

by:jyotisinha
jyotisinha earned 160 total points
ID: 13527861
I don't think it's possible to pass a string parameter as in your case to SQL server SP using single parameter because when a tring parameter is passed from .Net, it adds additional single quote(') before and after the parameter value thus comma separated values become single value for SQL server.

one possible way that comes to me is that use this in query form from ASP.Net with multiple parameter as
make the query as -

lets say strParamList is the comma separated list of parameters with values "Bikes,Cars,Mountains"

string[] arrParams = strParamList.Split(new char[]{','});

string strQuery="SELECT * FROM qheader WHERE Category IN("
for (int ctr=0;ctr<arrParams.Count;ctr++)
{
  strQuery+= strQuery +"@"+tr.ToString()+",")
}
strQuery= strQuery.Substring(0,strQuery.Length-1) + ")" //remove the extra comma and
                                                                                  //close the parantheses

command1.CommandText=strQuery;
//the commandtext would be like - SELECT * FROM qheader WHERE Category IN(@0,@1,@2..@n-1)

and while passing the parameters use it in loop -

SqlParameter objParam=null

for (int i=0;i<arrParams.Count;i++)
{
   objParam=new SqlParameter("@" + i.ToString(),arrParams[i]);
   command1.Parameters.Add(objParam);

}
command1.ExecuteNonquery();

hope this helps
Jyoti.


0
 
LVL 34

Expert Comment

by:arbert
ID: 13528022
"parameter is passed from .Net, it adds additional single quote(') before and after the parameter value "

What??

This has been covered a couple of times here:  http://www.experts-exchange.com/Databases/Q_21241611.html

Brett
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 340 total points
ID: 13528753
you need to use dynamic sequel if you want it to work as described

CREATE PROCEDURE sproc_GetCategories
     @Categories varchar(250)
AS

DECLARE @SQL as nvarchar(8000)
SET @SQL = 'SELECT * FROM qheader WHERE Category IN (' + @Categories + ')'
EXEC @SQL
GO

The reason is that if you just @categories in your selection as you were then it is looking for qheaders in a string that happens to have commas in it not a comma-delimited list of values.
0
 
LVL 6

Expert Comment

by:jyotisinha
ID: 13529449
"parameter is passed from .Net, it adds additional single quote(') before and after the parameter value "

What??

okay....okay...arbert ...there is nothing to be so aghasted about...i didn't try that...nathc you can try that too.

hope this works for you
Jyoti.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13530592
"there is nothing to be so aghasted about"

Wasn't aghasted, just wondered if you were sure about what you typed...
0
 

Author Comment

by:nathc
ID: 13530861
Hi everyone,

I browsed the web relentlessly last night and found a solution almost identical to yours BriCrowe. And learned a lot about building dynamic SQL queries too.
I ended up using syntax similar to this..

CREATE PROCEDURE sproc_GetCategories
     @Categories varchar(250)
AS

DECLARE @SQL as nvarchar(8000)
SET @SQL = 'SELECT * FROM qheader WHERE Category IN (' + @Categories + ')'
EXEC @SQL
GO

I'll split the points.

Thanks
0
 
LVL 34

Expert Comment

by:arbert
ID: 13530907
Glad you got it.  The post that I posted above  (about 5hours before Bri) shows dynamic sql....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question