Select query using varchar with 'in'

Question

I have this query:

DECLARE @holdIds VARCHAR(MAX)

SET @holdIds = '1,2,3'

DECLARE @flagNames NVARCHAR(MAX);

SELECT
    @flagNames = COALESCE(@flagNames + ',', '') + FlagName
FROM
    BurnHoldStatus
WHERE
    BurnHoldStatusID in (@holdIds);

SELECT @flagNames AS FlagName;

In this example the variable '@holdIds' has values '1,2,3' but could have just one value '1'.

When I run the query, an error appears:

Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value '1,2,3' to data type int.

I try convert the value of '@holdIds' but not work.

Any idea?

Thanks.

[UPDATE]

I found the answer:

DECLARE @holdIds NVARCHAR(MAX);
SET @holdIds = '1,2,3';

DECLARE @holdIdList TABLE(id INT);

INSERT INTO @holdIdList 
    SELECT * FROM Split(@holdIds, ',');

DECLARE @flagNames NVARCHAR(MAX);

SELECT 
    @flagNames = COALESCE(@flagNames + ',', '') + FlagName 
FROM 
    BurnHoldStatus, @holdIdList h 
WHERE 
    BurnHoldStatusID = h.id;

SELECT @flagNames AS FlagName;

In this code I use an function 'Split' to split a string passing the divisor (i.e: ',').

Split function code:

    ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(MAX),
    @SplitOn nvarchar(MAX)
)  
    RETURNS @RtnValue table 
(
    Data nvarchar(MAX)
) 
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END

Thanks for Vercelli by show other post.

Thanks guys :)

Check this:http://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable — vercelli, Sep 01 '16 at 16:28
what you define is a comma seperated list of values as a string. what "IN" needs is an array of integer — swe, Sep 01 '16 at 16:28
The answer marked as correct in the question that @vercelli linked is not a good approach. You should avoid dynamic sql for this type of thing. A string splitter would be a better approach. http://sqlperformance.com/2012/07/t-sql-queries/split-strings And even better approach would be to use table valued parameters. — Sean Lange, Sep 01 '16 at 16:39
Where does this `1,2,3` come from? If from an app, I [strongly recommend TVPs](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) instead of a comma-separated list. If you need to keep the comma-separated list, [you need a splitting function](http://sqlperformance.com/2012/07/t-sql-queries/split-strings). If you are using SQL Server 2016, there is [a new built-in function that performs quite well called `STRING_SPLIT()`](http://sqlperformance.com/?s=string_split). — Aaron Bertrand, Sep 01 '16 at 16:39
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Sep 01 '16 at 17:21

score 0 · Answer 1 · answered Sep 01 '16 at 16:47

The comment made by swe is correct. You could make the query dynamic, then insert @holdIds See below for a workaround:

DECLARE @holdIds VARCHAR(MAX)

SET @holdIds = '1,2,3'

SET @holdIds = (CHAR(39) + (REPLACE(@holdIds, ',', ''',''') + CHAR(39)))

You can do this, then set the entire query above as a varchar variable, then execute. There are certainly other workarounds as well but dynamic SQL will work.

Select query using varchar with 'in'

1 Answers1