• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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

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
nathc
Asked:
nathc
2 Solutions
 
sajuksCommented:
have you defined length for the input paramter @Categories ?/
for ex :create procedure usp_myproc( @categories varchar(10))
0
 
jyotisinhaCommented:
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
 
arbertCommented:
"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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Brian CroweDatabase AdministratorCommented:
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
 
jyotisinhaCommented:
"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
 
arbertCommented:
"there is nothing to be so aghasted about"

Wasn't aghasted, just wondered if you were sure about what you typed...
0
 
nathcAuthor Commented:
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
 
arbertCommented:
Glad you got it.  The post that I posted above  (about 5hours before Bri) shows dynamic sql....
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now