Using IN in a where clause from ASP .net
-
Hi, I have a SP that shoyuld receive a string and return a record set as follows
Create SP mySP
@codeList char(50) = Null as
Select * from myTable WHERE m_NAME IN (@codeList)
From asp .net when setting the value for the parameter = "APP", I get a record with one line. But when giving it 2 codes like this = "APP, DDC", I get 0 record back
I tried "'APP', 'DDD'", without success.
Thanks for your help
-
Try this
Select * from myTable WHERE CHARINDEX(',' + m_NAME + ',' , ',' + @codeList + ',') > 0
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry but it doesn't work the way you assume, in your example, @codeList can only be a single value not a list. There are a few options:
if you know that the won't be moe than say 3 values then try:
Select * from myTable WHERE m_NAME IN (@codeList1,@codeList3,@codeList3)
Or build dynamic SQL either in the client ot in SQL
Set @sql = 'Select * from myTable WHERE m_NAME IN ('
+ @codeList + ')'
Exec (@SQL)
or, if you ensure your parameter has a leading and trailing comma (,) or other delimited e.g. ,APP,DDC,
Select * from myTable WHERE @codeList like '%,' + m_NAME + ',%'
or using double-quotes as the delimiter
e.g. "APP","DDC"
Select * from myTable WHERE @codeList like '%"' + m_NAME + '"%'
Watch for the double & single quites above.
it reads Single % Double single + m_NAME + single double % single
Hope this gives you a few ideas...
-
Here's some code I use. It requires creating two UDFs, dbo.fGetToken and dbo.fGetTable. UDF dbo.fGetTable references dbo.fGetToken.
All of this boils down to being able to execute a query like this:
SELECT m.*
FROM myTable m
JOIN dbo.fGetTable(@codelist, ',') c ON m.m_name = c.code
--------------------------------------------------------------
DROP FUNCTION dbo.fGetTable
DROP FUNCTION dbo.fGetToken
GO
CREATE FUNCTION dbo.fGetToken
(
@parm varchar(8000),
@delim varchar(100),
@whichOccur smallint
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @occur int, @spos int
DECLARE @token varchar(8000)
SET @occur = 0
WHILE @occur < @whichOccur AND @parm <> '' AND @parm IS NOT NULL
BEGIN
SET @spos = CHARINDEX( @delim , @parm, 1 )
IF @spos = 0
BEGIN
SET @token = @parm
SET @parm = ''
END
ELSE
BEGIN
SET @token = SubString( @parm, 1, @spos - 1)
SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )
END
SET @occur = @occur + 1
END
IF @occur <> @whichOccur
SET @token = '' -- or NULL
RETURN @token
END
GO
CREATE FUNCTION dbo.fGetTable
(
@codelist varchar(100),
@delim varchar(10)
)
RETURNS @tbl TABLE (code varchar(10))
AS
BEGIN
DECLARE @code varchar(10), @occur int
SET @occur = 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
WHILE @code <> ''
BEGIN
INSERT @tbl (code) VALUES (@code)
SET @occur = @occur + 1
SET @code = dbo.fGetToken(@codeList, @delim , @occur)
END
RETURN
END
GO
---------------------------------------------------------------------
-- EXAMPLE:
---------------------------------------------------------------------
DROP TABLE myTable
GO
CREATE TABLE myTable
(
id int IDENTITY(1,1) PRIMARY KEY,
m_name varchar(10)
)
SET NOCOUNT ON
INSERT myTable (m_name) VALUES ('APP')
INSERT myTable (m_name) VALUES ('DDC')
INSERT myTable (m_name) VALUES ('ABC')
INSERT myTable (m_name) VALUES ('JKLMNO')
INSERT myTable (m_name) VALUES ('STU')
INSERT myTable (m_name) VALUES ('YZ')
INSERT myTable (m_name) VALUES ('123')
INSERT myTable (m_name) VALUES ('456')
SET NOCOUNT OFF
GO
DECLARE @codeList varchar(50)
--SET @codeList = 'APP'
--SET @codeList = 'APP, DDC'
SET @codeList = 'APP, DDC, ABC, DEF, GHI, JKLMNO, PQR, STU, VWX, YZ'
-- Run the query
SET @codeList = Replace(@codeList, ' ', '')
SELECT m.*
FROM myTable m JOIN dbo.fGetTable(@codelist, ',') c ON m.m_name = c.code
-
Read this:
http://www.sommarskog.se/arrays-in-sql.html
and this:
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks everybody, I was in vacation so that's why I didn' respond, I liked the fgettoken and fgettable functions, it works fine.
Also the articles on sommarskog are wonderfull
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply