I am not getting values by passing variable using IN query in SQL

Question

I am passing string values from my code like '12th Standard/Ordinary National Diploma,Higher National Diploma' to SQL query, but I am not getting any values and nothing showing any result.

My SQL query:

declare @qua varchar(250),@final varchar(250),@Qualification varchar(250)
set @Qualification= '12th Standard/Ordinary National Diploma,Higher National Diploma'
set @qua =replace(@Qualification,',',''',''')
set @final= ''''+@qua+''''

select  * from mytablename in(@final)

Result: Data is not displaying

Thank you in advance.

I would create a temp table for your comma seperated values and then would join with your actual table to get the data. — SMA, Dec 23 '15 at 13:35
Possible duplicate of [Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Lukasz Szozda, Dec 23 '15 at 13:37

score 3 · Accepted Answer · answered Dec 23 '15 at 13:37

Instead do it using a table variable like

declare @tbl table(qual varchar(250));
insert into @tbl
select '12th Standard/Ordinary National Diploma'
union
select 'Higher National Diploma';
select  * from mytablename where somecolumn in(select qual from @tbl);

score 2 · Answer 2 · answered Dec 23 '15 at 13:38

2

Despite trying to put quote marks in there, you're still only passing a single string to the IN. The string just contains embedded quotes and SQL Server is looking for that single long string.

You also don't seem to be comparing a column for the IN.

Your best bet is to pass in multiple string variables, but if that's not possible then you'll have to write a function that parses a single string into a resultset and use that. For example:

SELECT
    Column1,    -- Because we never use SELECT *
    Column2
FROM
    MyTableName
WHERE
    qualification IN (SELECT qualification FROM dbo.fn_ParseString(@qualifications))

answered Dec 23 '15 at 13:38

Tom H

46,766
14
87
128

Can you write function fn_ParseString for above code. – Mr doubt Dec 30 '15 at 09:28
Did you search for such a function? Both Stackoverflow and Google will yield results if you simply look. For example, http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco – Tom H Dec 30 '15 at 13:50

score 1 · Answer 3 · answered Dec 23 '15 at 13:53

You can insert all your search criteria in one table and then can easily do a lookup on the main table, example below:

 DECLARE @MyTable TABLE (Name VARCHAR(10), Qualification VARCHAR(50))
   DECLARE @Search TABLE (Qualifications VARCHAR(50))

   INSERT INTO @MyTable VALUES ('User1','12th Standard'), ('User2','Some Education'),
                            ('User3','Ordinary National Diploma'), ('User4','Some Degree'),
                            ('User5','Higher National Diploma')

    INSERT INTO @Search VALUES ('12th Standard'),('Ordinary National Diploma'),('Higher National Diploma')

    SELECT MT.*
    FROM @MyTable MT
        INNER JOIN (SELECT Qualifications FROM @Search)  S ON S.Qualifications = MT.Qualification

score 0 · Answer 4 · answered Dec 23 '15 at 13:51

As previous said, you are passing a string with commas, not comma separated values. It needs to be split up into separate values.

You can do this by passing the qualification string into XML which you can use to turn it into separate rows of data. The IN parameter will then accept the data as separate values.

DECLARE @Qualifications as varchar(150) = '12th Standard/Ordinary National Diploma,Higher National Diploma'
Declare @Xml XML;
SET @Xml = N'<root><r>' + replace(@Qualifications, char(44),'</r><r>') + '</r></root>';
select *
from MyTableName
Where MyTableName.Qualification in 
    (select r.value('.','varchar(max)') as item
    from @Xml.nodes('//root/r') as records(r))

score 0 · Answer 5 · answered Dec 23 '15 at 13:54

Alternatively you can create a table-valued function that splits according to input like in your case its ',' and then INNER JOIN with the returnColumnname and that particular column that you want to filter

SELECT COLUMNS, . . . . 
FROM MyTableName mtn
    INNER JOIN dbo.FNASplitToTable(@qualifications, ',') csvTable 
    ON csvTable.returnColumnName = mtn.somecolumn

Table Valued function might be like:

CREATE FUNCTION dbo.FNASplitToTable (@string varchar(MAX), @splitType CHAR(1))
RETURNS @result TABLE(Value VARCHAR(100))
AS
BEGIN

      DECLARE @x XML 
      SELECT @x = CAST('<A>' + REPLACE(@string, @splitType, '</A><A>') + '</A>' AS XML)

      INSERT INTO @result            
      SELECT LTRIM(t.value('.', 'VARCHAR(100)')) AS inVal
      FROM @x.nodes('/A') AS x(t)
    RETURN
END   
GO

I am not getting values by passing variable using IN query in SQL

5 Answers5