CODEWITHSUNDEEP
c#sqlsql-serverin-clause
Patrick
Patrick

Reputation: 21

SQL stored procedure IN clause varchar array

i have a c# program and want to give a parameter to the stored procedure. The string in my c# program is like "'Food','Clothes','Blood'".

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND subCategory in (@categoryFilter) 
END

The result should give me 3 entries but the result is empty. I've tried to escape the parameters with two more ' but nothing changed in the result.

it works with int values on an other column but not with chars.

if anyone could help, that would be great :)

Upvotes: 2

Views: 2183

Answers (6)

Vinayaka
Vinayaka

Reputation: 1

you could try as below

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = '',
    @Query varchar(2000)

AS
BEGIN
    SET NOCOUNT ON;

    Set @Query = 'SELECT *
            FROM dbo.Locations
           WHERE longitude BETWEEN @longMin AND @longMax
                  AND latitude BETWEEN @latMin AND @latMax
                  AND @categoryFilter IN (' + LTRIM(RTRIM(@categoryFilter)) + ')' 

    Exec(@Query)

END

Upvotes: 0

roman
roman

Reputation: 117400

you could use LIKE:

ALTER PROCEDURE [dbo].[GetLocations]
    @longMax float =100,
    @longMin float=0,
    @latMax float=100,
    @latMin float=0,
    @categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND @categoryFilter LIKE '%' + subCategory + '%'
END

Upvotes: 0

bvr
bvr

Reputation: 4826

Try this

ALTER PROCEDURE [dbo].[GetLocations]
@longMax float =100,
@longMin float=0,
@latMax float=100,
@latMin float=0,
@categoryFilter char(200) = ''
AS
BEGIN
    SET NOCOUNT ON;
   SET @categoryFilter =  REPLACE(@categoryFilter,',',''',''')

   DECLARE @QUERY VARCHAR(MAX)
   SET @QUERY = 'SELECT *
    FROM dbo.Locations
    WHERE longitude BETWEEN @longMin AND @longMax
      AND latitude BETWEEN @latMin AND @latMax
      AND subCategory in ('''+RTRIM(@categoryFilter)+''')'

    --PRINT (@QUERY) 

    EXEC (@QUERY) 


END

Upvotes: 1

Matthias
Matthias

Reputation: 164

Try to Trim your incoming string -> RTRIM(@string).

If you're using char, u always have to trim.

Upvotes: 0

Veer
Veer

Reputation: 1593

Your are getting a blank result set because in clause works with set of data. And you are passing a single string of 200 words. Your query is actually comparing subCategory and categoryFilter

You can try explicitly in management studio by providing set as follows

SELECT *
FROM dbo.Locations
WHERE longitude BETWEEN @longMin AND @longMax
AND latitude BETWEEN @latMin AND @latMax
AND subCategory in ('abc','bcd','cde');

abc- your first category filter, similarly bcd your second and so on. If you are having set of data in @categoryfilter seperated by comma(,), then first split that string and then use IN Clause

Upvotes: 3

Devart
Devart

Reputation: 121932

I think you need split your string parameter -

DECLARE @categoryFilter VARCHAR(200) = 'Food,Clothes,Blood'

SELECT t.c.value('.', 'VARCHAR(50)')
FROM (
     SELECT ID = CAST ('<M>' + REPLACE(@categoryFilter, ',', '</M><M>') + '</M>' AS XML)
) r 
CROSS APPLY ID.nodes ('/M') t(c)

Your query -

ALTER PROCEDURE [dbo].[GetLocations] 

@longMax FLOAT = 100,
@longMin FLOAT = 0,
@latMax FLOAT = 100,
@latMin FLOAT = 0,
@categoryFilter VARCHAR(200) = 'Food,Clothes,Blood'

AS
BEGIN
     SET NOCOUNT ON;

     SELECT *
     FROM dbo.Locations
     WHERE longitude BETWEEN @longMin AND @longMax
          AND latitude BETWEEN @latMin AND @latMax
          AND (
               @categoryFilter = '' 
               OR 
               subCategory IN (
                    SELECT t.c.value('.', 'VARCHAR(50)')
                    FROM (
                         SELECT ID = CAST ('<M>' + REPLACE(@categoryFilter, ',', '</M><M>') + '</M>' AS XML)
                    ) r 
                    CROSS APPLY ID.nodes ('/M') t(c))
          )

END

Upvotes: 2

Related Questions