SQL Query execution with IN statement

Question

I didn't get proper result

Below is my tables values :

DECLARE @Id as nvarchar(MAX) = N'''2'',''3'''
DECLARE @spparams as nvarchar(max) =N'@Id as nvarchar(max)'
EXEC sp_executesql N'select * from temp_table where cast(Id as nvarchar(50)) in (@Id)',@spparams,@Id= @Id

SQL Fiddle

I am not able to get result. Can someone help me with this?

I know there is some mistakes in single quotes only, I tried so many ways but not getting results.

http://sqlfiddle.com/#!18/a400f/1

Answer 1

N'''2'',''3''' is a scalar value - as in a string: "'2','3'". SqlServer treats it as a singular string, when you use it as a parameter in your query.

One solution could be to use WHERE IN (SELECT value FROM string_split(@Id, ',')). which would split it into two strings: ["'2'","'3'"] - but again: strings with literal single-ticks in them. So when you CAST(Id as NVARCHAR(50)) you get your Ids as strings, but without the ticks: ["1","2","3"], which of course don't match.

So:

DECLARE @Id AS nvarchar(MAX) = N'2,3'
DECLARE @spparams as nvarchar(max) =N'@Ids nvarchar(MAX)'
EXEC sp_executesql N'SELECT * FROM temp_table where cast(Id AS nvarchar(50)) IN (SELECT value FROM string_split(@Ids,'',''))',@spparams,@Ids= @Id

An even better solution is to not treat numbers as strings

DECLARE @Id AS nvarchar(MAX) = N'2,3'
DECLARE @spparams as nvarchar(max) =N'@Ids nvarchar(MAX)'
EXEC sp_executesql N'SELECT * FROM temp_table where Id IN (SELECT convert(int, value) FROM string_split(@Ids,'',''))',@spparams,@Ids= @Id

Answer 2

Your scalar string is just that: a string. The compiler doesn't embed your value directly into your query. It creates a placeholder string variable during compilation, then binds the value directly to that variable as a single string.

---

The real solution here is to keep everything set-based by using a Table Valued Parameter, rather than messing around with strings.

First create the table type:

CREATE TYPE dbo.IntList (Value int PRIMARY KEY)

DECLARE @Id dbo.IntList;
INSERT @Id(Value)
VALUES (2),(3;

DECLARE @sql nvarchar(max) = N'
SELECT *
FROM temp_table
where Id IN (
  SELECT value
  FROM @Ids
);
'

EXEC sp_executesql
  @sql,
  N'@Ids dbo.IntList',
  @Ids = @Id;

Although it's unclear why you need dynamic SQL here, perhaps it's a minimal example.