0

I didn't get proper result

Below is my tables values : enter image description here

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

1

2 Answers 2

2

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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.