CODEWITHSUNDEEP
sql-servert-sql
SQLSam
SQLSam

Reputation: 537

Calculated Parameter in WHERE

I am trying to use a carriage return separated list of parameters in an IN list of the where statement of my query.

I can turn the list into one comma separated string in the correct format using replace function, however when I put this in the IN list, it returns nothing.

The query below returns the comma separated list as expected.

declare @VarCodes varchar(max)
set @VarCodes = '123-1
123-10
123-100
61
66
67
75'
(select  ''''+replace(replace(REPLACE(@VarCodes,char(13),''''+', '+''''),char(32),''),char(10),'')+'''')

'123-1','123-10','123-100','61','66','67','75'

If I paste this text directly in the query below, it returns data as expected.

select vad_variant_code from  variant_detail where vad_variant_code in ('123-1','123-10','123-100','61','66','67','75')

If I put the parameter in the in, it returns nothing. 

select vad_variant_code from  variant_detail where vad_variant_code in ((select  ''''+replace(replace(REPLACE(@VarCodes,char(13),''''+', '+''''),char(32),''),char(10),'')+''''))

I am assuming this is because the IN is expecting a comma separated list of strings, where as the replace function is returning one long string?

Can this be achieved?

Upvotes: 0

Views: 69

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23807

I have this code as a TVF based originally on Jeff Moden's code:

CREATE FUNCTION [dbo].[cSplitter] (@Parameter VARCHAR(MAX))
RETURNS @splitResult TABLE (number INT, [value] VARCHAR(100))
AS
BEGIN
SET @Parameter = ','+@Parameter +',';

WITH cteTally AS
    (
        SELECT TOP (LEN(@Parameter))
            ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
            FROM Master.sys.All_Columns t1
            CROSS JOIN Master.sys.All_Columns t2
    )
INSERT @splitResult
    SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
    SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS [Value]
    FROM cteTally
        WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ','
RETURN
END

With this TVF in my database, my "IN" queries can accept a comma separated list of values like this:

DECLARE @VarCodes VARCHAR(MAX);
SET @VarCodes = '123-1
123-10
123-100
61
66
67
75';

DECLARE @csv VARCHAR(MAX);
SET @csv = REPLACE(REPLACE(REPLACE(@VarCodes, CHAR(13), ','), CHAR(32), ''),
                   CHAR(10), '');

SELECT  vad_variant_code
FROM    variant_detail
WHERE   EXISTS ( SELECT *
                 FROM   [cSplitter](@csv) AS [cs]
                 WHERE  [cs].[value] = vad_variant_code );

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

Try this...

declare @VarCodes varchar(max), @Xml XML;
set @VarCodes = '123-1,123-10,123-100,61,66,67,75'

SET @Xml = N'<root><r>' + replace(@VarCodes, ',','</r><r>') + '</r></root>';


select vad_variant_code from  variant_detail 
where vad_variant_code in (
                           select r.value('.','varchar(max)') as item
                           from @Xml.nodes('//root/r') as records(r)
                          )

Upvotes: 1

Related Questions