0

This quite difficult to explain, but I will have a go. I have a SQL query (using SQL Server) like below:

declare @recordid as nvarchar(50)
set @recordid = 1,2,3

select * from customers
where recordid in (@recordid)

As you can see the syntax near 1,2,3 is incorrect. The reason that the code says 1,2,3 rather than '1,2,3' (with quotes) is that it is part of a system that builds the query dynamically and it just places in 1,2,3. The 1,2,3 could be a variable number of values e.g. 3,5,7,2,6,3,6,7,8, potentially hundreds.

I need to change 1,2,3 in to something that SQL Server can understand and include in the select statement.

One idea I had was to insert it into a common table expression, but I can not find the correct syntax to do this. Example below, I know this is incorrect syntax:

with test (a) as (
select 1,2,3
)

select * from customers
where recordid in (test)

Does anyone have any ideas how I can change this value in to something SQL can understand?

user85567
  • 3
  • 3
  • 1
    *"The reason that the code says 1,2,3 rather than '1,2,3' (with quotes) is that it is part of a system that builds the query dynamically"* - Well, that system must be changed. Can you control that? – Zohar Peled Dec 06 '19 at 08:29
  • 1
    You state, that this *is part of a system that builds the query dynamically*... Is this under your control? The given query might look like SQL but is plain wrong... You will either have to change the statements creation or you will have to change the statement with string methods... – Shnugo Dec 06 '19 at 10:08
  • Does this answer your question? [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – Eric Brandt Dec 06 '19 at 13:42

1 Answers1

0

You can use STRING_SPLIT like

select * from customers
where recordid in (select recordid from STRING_SPLIT(@recordid,','))
erdi yılmaz
  • 352
  • 1
  • 4
  • 15