Calculated Parameter in WHERE

Question

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?

1) Dynamic SQL 2) Table-Valued Parameter 3) Table valued split function ... — Lukasz Szozda, Nov 16 '15 at 15:52
Possible duplicate of [Define variable to use with IN operator (T-SQL)](http://stackoverflow.com/questions/1707326/define-variable-to-use-with-in-operator-t-sql) — Lukasz Szozda, Nov 16 '15 at 15:53

score 1 · Accepted Answer · answered Nov 16 '15 at 16:01

1

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)
                          )

answered Nov 16 '15 at 16:01

M.Ali

67,945
13
101
127

Thank you. Speed is not really an issue here as there will be max of 20 variables. I have modified your select slightly, as my list is not character separated, rather carriage return. Not a problem, I've simply changed the replace inside the XML selection. SET @Xml = N'' + replace(replace(@VarCodes,char(10),''), char(13),'') + ''; – OWSam Nov 16 '15 at 16:31

score 0 · Answer 2 · answered Nov 16 '15 at 16:07

0

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 );

answered Nov 16 '15 at 16:07

Cetin Basoz

22,495
3
31
39

It cool but a lot of pain for a fairly simple task :( , I did the whole thing in two lines see my answer :) – M.Ali Nov 16 '15 at 16:09
Having less lines doesn't mean that it is better. You are using xml which I wouldn't suggest in this case, plus you are assuming it is comma separated value but it is not (see the OP). Actually, I was lazy to add more to that, since SQL server is very slow at string operations I would really suggest creating that TVF as a CLR function (which I did in real life - there is a vewry huge performance difference, with xml it is being the worse). – Cetin Basoz Nov 16 '15 at 16:14
True having less lines of code doesnt mean it is going to be better in performance too, but the code you have suggested, is doing string manipulation , character by character, I reckon anything would be faster than this :) – M.Ali Nov 16 '15 at 16:18
`I would really suggest creating that TVF as a CLR function` how do you create a TVF as CLR function ??? it just doesnt make any sense at all ???? – M.Ali Nov 16 '15 at 16:20
OK I mean I don't suggest XML for this thing anyway (in forums actually I list and sample that option as 3rd or 4th). Here is a forum message about it with the CLR codes and samples included (story part is in Turkish but what you need is the code part anyway): https://social.msdn.microsoft.com/Forums/tr-TR/715c93c3-23ee-4f7b-af65-45eeb7d24865/checkboxlist-deki-seili-olan-itemlera-gre-arama-yapma-yardm?forum=csharptr – Cetin Basoz Nov 16 '15 at 16:21
Unfortunately I cannot insert functions into the database. – OWSam Nov 16 '15 at 16:24
Why would you ever think that creating a TVF as a CLR function doesn't make any sense at all???? If you want speed you surely would want it (I mean speeds where CLR version generally takes under 100 ms where the non-CLR approach is likely to time out with default connection timeout - been there done that). – Cetin Basoz Nov 16 '15 at 16:26
Then you could have that as part of your SQL code or maybe than you are doing all these from a .Net language, you could use a Linq query with up to 2100 values (SQL server parameter limit). – Cetin Basoz Nov 16 '15 at 16:29

Calculated Parameter in WHERE

2 Answers2