In keyword not working properly in SQL Server?

Question

I am working on SQL Server 2008 R2.

The following query:

select alloted_area 
from tbllogin 
where nid = 82

returns this

alloted_area
1,101,1002,79,365,297

The following query

select * 
from tblarea 
where nid in (1, 101, 1002, 79, 365, 297)

returns this

nid     area_name
------------------
1       RAJASTHAN
101     RAJASTHAN
79      RAJASTHAN
297     RAJASTHAN
365     RAJASTHAN
1002    RAJASTHAN

But the following query

select * 
from  tblarea 
where nid in (select alloted_area from tbllogin where nid = 82)

generates this error:

Conversion failed when converting the varchar value '1,101,1002,79,365,297' to data type int

What should I do?

Perhaps I have to make rows out of alloted_areas in tbllogin on the basis of comma sign. How do I do that?

As suggested in the comments I did try not to store data as comma separated values but that would mean creating an entire table to store that information. So is there any way I can achieve the desired result in my situation.

Never, ever store data as comma separated values. It will only cause you lots of trouble! — jarlh
– jarlh, Commented Apr 18, 2017 at 7:23
IN is working properly. What you are doing is the equivalent of nid = cast( '1,101,1002,79,365,297' as int), which of course doesn't work. — Mark Rotteveel
– Mark Rotteveel, Commented Apr 18, 2017 at 7:25

Dheeraj Sharma · Accepted Answer · 2017-04-18 07:34:22Z

2

Try this:

DECLARE @values NVARCHAR(100) = (select alloted_area from tbllogin where nid = 82)
DECLARE @sqlQuery NVARCHAR(200) = ' select *  from  tblarea  where nid in ( '+ @values +' ) '
EXEC (@sqlQuery)

Hope it helps. :)

edited Apr 18, 2017 at 7:34

answered Apr 18, 2017 at 7:30

Dheeraj Sharma

7091 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rexroxm Over a year ago

it returns this error Could not find stored procedure ' select * from tblarea where nid in ( 1,101,1002,79,365,297 ) '.

BeanFrog Over a year ago

Change EXEC @sqlQuery to put brackets around the variable: EXEC (@sqlQuery)

Dheeraj Sharma Over a year ago

Yea, beanfrog said is true. sorry i forgot the paren. :)

Naveen · Accepted Answer · 2017-04-18 07:48:41Z

1

this will work

DECLARE @a VARCHAR(max) = (select alloted_area from tbllogin where nid = 82)
select * from  tblarea where nid in (select sID from splitstring(@values,','))

answered Apr 18, 2017 at 7:48

Naveen

2941 silver badge11 bronze badges

Comments

t-clausen.dk · Accepted Answer · 2017-04-18 08:40:45Z

Here are 2 different methods without involving a variable: The SplitString is a new feature for sqlserver 2016, so I included a method to implement StringSplit for sqlserver 2008.

First method will work as long as nid in the table tbllogin is unique:

SELECT
  * 
FROM
  tblarea 
WHERE
  nid in 
    (
      SELECT Value
      FROM
        [SplitString]((SELECT alloted_area FROM tbllogin WHERE nid = 82),',')
    )

Second method is able to handle situations when nid in table tbllogin is not unique:

SELECT
  * 
FROM
  tblarea 
WHERE
  nid in 
  (
    SELECT x.value
    FROM tbllogin
    CROSS APPLY 
      [SplitString](alloted_area,',')x
   WHERE nid = 82
)

You can use this function to imitate StringSplit in sqlserver 2008:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
    );

Simran · Accepted Answer · 2017-04-18 07:27:32Z

-1

nId is an int type. It looks like alloted_area is varchar type. Cross check.

answered Apr 18, 2017 at 7:27

Simran

1021 gold badge1 silver badge8 bronze badges

Comments

nick · Accepted Answer · 2017-04-18 07:37:39Z

-1

This might be because the type int is not declared correctly in your sub-select statement. This is where the cast() method works well.

Try: cInt(alloted_area) AS alloted_area_int

select * from tblarea where nid in (select cInt(alloted_area) AS alloted_area_int from tbllogin where nid=82)

answered Apr 18, 2017 at 7:37

nick

1

Collectives™ on Stack Overflow

In keyword not working properly in SQL Server?

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related