Link to home
Start Free TrialLog in
Avatar of nathc
nathc

asked on

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?
Avatar of sajuks
sajuks

have you defined length for the input paramter @Categories ?/
for ex :create procedure usp_myproc( @categories varchar(10))
SOLUTION
Avatar of jyotisinha
jyotisinha

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"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:  https://www.experts-exchange.com/questions/21241611/SQL-Server-Dynamic-Query-using-IN.html

Brett
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"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.
"there is nothing to be so aghasted about"

Wasn't aghasted, just wondered if you were sure about what you typed...
Avatar of nathc

ASKER

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
Glad you got it.  The post that I posted above  (about 5hours before Bri) shows dynamic sql....