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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.
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...
Wasn't aghasted, just wondered if you were sure about what you typed...
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
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....
for ex :create procedure usp_myproc( @categories varchar(10))