passing my comma separated string won't generate results in SQL

Question

I Have a need to pass a comma delimited string as a parameter to a stored procedure. That string contains ints seperated by commas. Then I need to use the ints in an IN clause, like, "where ReferenceValue in ( '50022056', '50022073', '50022075', '50022072', '50022086', '50022095', '50022074', '50022077', '50022100', '50022101', '50022106', '50022107', '50022108', '50022109', '50022110', '50022111', '50022112', '50022113', '50022115', '50022116', '50022117', '50022118', '50022119' )"

Now I have a parameter I am passing to a stored proc containing string parameter ="50001362,50001414,50001437,50001630,50001643,50001659,50001775,50001272,50001276,50001220,500012226"

I have tried using Dynamic SQL and doing where ReferenceValue in ( '+@groupNumbers+' ) however, i get back no results

At the top of my stored proc i am declaring the variable

DECLARE @groupNumbers VarChar(1000)
SET @groupNumbers = '50008300,50002427'

The SET is just a test case to see how I can get back results with a comma delimited string. I need to pass in my string of group numbers into this parameter and get back results. Currently i Am looping through each group number and calling the query with each group number.

Any advice on what i am doing wrong? I am very novice at SQL.

Thank you

can you check http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause — Sebri Zouhaier, Sep 30 '15 at 17:58
Thank you for that link, I will check into it. never created a function within sql server management studio, I will attempt to create this function and hopefully close this thread. — devzim, Sep 30 '15 at 18:01
see this also http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S — Sebri Zouhaier, Sep 30 '15 at 18:02
Maybe you can consider use Table-Valued Parameters that pass multiple value to SP? https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx — EricZ, Sep 30 '15 at 18:06
I believe TVP's are more useful in the scenario of an actual data type and not a delimited list of data. Meaning you could theoretically pass in a Customer as a TVP, where the Customer is made up of many different types, which must be declared. If the user above has a varying number of elements in his list this would not be a viable solution. — ewahner, Sep 30 '15 at 18:27
Ouch. So many answers about this that are using loops. There is no need for any kind of loop or recursive cte to solve this kind of thing. Those types of splitters are awful for performance. Here is an article with a number of better approaches. http://sqlperformance.com/2012/07/t-sql-queries/split-strings or you can check out my favorite splitter. http://www.sqlservercentral.com/articles/Tally+Table/72993/ — Sean Lange, Sep 30 '15 at 18:31

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

Please refer to my answer in this other SO post How to Convert a delimited string to a list

Then after you have decided to either use the CLR method or the SQL UDF method you can then do the following:

select *
  from dbo.YourTableHere t
 inner join dbo.fnArray(@groupNumbers, ',') g on t.somefield = convert(int, g.arrValue)

Should get you what you want.

edited May 23 '17 at 12:29

Community

1
1

answered Sep 30 '15 at 18:01

ewahner

1,149
2
11
23

The function has been created and i can see the function underneath. Programmability.Functions.Table-valued Functions dbo.fnArray. When i call it where ReferenceValue in ( dbo.fnArray(@groupNumbers, ',') ) I get Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnArray", or the name is ambiguous. – devzim Sep 30 '15 at 18:27
1

Did you create it as dbo? To run a simple test...just do the following: `select * from dbo.fnArray('this,is,awesome', ',')` – ewahner Sep 30 '15 at 18:37
Yes that works but when i use it within the in clause it does not work, nice method btw. – devzim Sep 30 '15 at 18:39
where ReferenceValue in ( dbo.fnArray(@groupNumbers,',') ) I get Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnArray", or the name is ambiguous. – devzim Sep 30 '15 at 18:40
I got it. I did not realize i had to use select * from – devzim Sep 30 '15 at 18:41
Thank you for posting the simple test. Once again, I am very novice at writing SQL. I normally only focus on writing c# while DBA's handle the sql side. – devzim Sep 30 '15 at 18:42
This is what worked. where ReferenceValue in ( select * from dbo.fnArray(@groupNumbers,',') ) – devzim Sep 30 '15 at 18:43
BTW you can customize the function...currently it removes dups from the list and capitalizes everything. – ewahner Sep 30 '15 at 18:45
Sweet, once again your awesome and thank you for bearing with me :) – devzim Sep 30 '15 at 18:51

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

base on solution described here Using-comma-separated-value-parameter-strings-in-S

/****** Object:  UserDefinedFunction [dbo].[CSVToTable]    Script Date:     04/28/2013 10:45:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END
GO

Simple:

DECLARE @LIST VARCHAR(200)

SET @LIST = '1,3'

SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

Please see my comment on the original post. Using a loop is about the worst possible way to do this from a performance perspective. — Sean Lange, Sep 30 '15 at 18:33

passing my comma separated string won't generate results in SQL

2 Answers2