How do I return a string of numbers separated by comma as an array parameter in SQL

Question

I want to create a stored procedure with the following parameter: @WorkspaceID int

What I have now is this:

DECLARE @WorkspaceRuleIds varchar(max) 

SELECT @WorkspaceRuleIds = COALESCE(@WorkspaceRuleIds + ', ', '') + 
   CAST(RuleID AS varchar(5))
FROM Dgn_Workspace_Rules
WHERE WorkspaceID = @WorkspaceID;

SELECT ag.* 
FROM Dgn_Rules ag
LEFT JOIN Dgn_Workspace_Rules wr ON ag.RuleID IN (@WorkspaceRuleIds)
WHERE wr.WorkspaceID = @WorkspaceID

If @WorkspaceID receives value of 25 then ag.RuleID IN (80,82) should get as parameters... but I get this error instead

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value '80,82' to data type int.

How do I fix this? Thanks!

The syntax and error looks like SQL Server, yet you tagged mysql. Which is it? — Remus Rusanu
– Remus Rusanu, Commented Sep 26, 2014 at 7:11
declare X as varchar, set X = '1,2,3,a,list,with,commas'; how many integers exist in X? (Answer: zero) You have ONE varchar parameter so how is it magically turned into a series of integers? forget about what it LOOKS like, think about the definitions. — Paul Maxwell
– Paul Maxwell, Commented Sep 26, 2014 at 7:14
I can't fathom what you're trying to do here - why is this not just a single SELECT with a join of ag.RuleID = wr.RuleID? I can't see why you're trying to mangle these rule IDs into a string just to then turn around and have problems with trying to pull the values back out of that string. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 26, 2014 at 7:21

Remus Rusanu · Accepted Answer · 2014-09-26 07:19:44Z

4

there are several well know techniques to concatenating row values. Read Concatenating Row Values in Transact-SQL for pros and cons of each.
ag.RuleID IN (@WorkspaceRuleIds) this does not do anything close to what you expect. It is not a macro substitution. It is a check for the IN predicate with the scalar value @WorkspaceRuleIds. Therefore @WorkspaceRuleIds will be coerced to an int, according to the Data Type Precedence Rules. Which results in the cast error you see.
Your queries makes absolutely no sense. JOIN on an IN condition?
NEVER use comma separated lists in SQL. NEVER.

answered Sep 26, 2014 at 7:19

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cristian Onescu · Accepted Answer · 2014-09-26 08:11:28Z

Thanks guys for your help but.. I found an answer for my question.

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

then execute this:

DECLARE @WorkspaceRuleIds varchar(max)

SELECT @WorkspaceRuleIds = COALESCE(@WorkspaceRuleIds + ', ', '') + 
   CAST(RuleID AS varchar(5))
FROM Dgn_Workspace_Rules
WHERE WorkspaceID = @WorkspaceID;

SELECT
ag.*
FROM
Dgn_Rules ag
LEFT JOIN
Dgn_Workspace_Rules wr
ON
ag.RuleID IN (SELECT * FROM dbo.CSVToTable(@WorkspaceRuleIds))
WHERE
wr.WorkspaceID = @WorkspaceID

and it outputs exactly what I need.

G J · Accepted Answer · 2014-09-26 08:23:12Z

0

Try the below query

SELECT
    ag.* 
FROM Dgn_Rules AS ag
LEFT JOIN Dgn_Workspace_Rules AS wr ON wr.RuleID = ag.RuleID
WHERE wr.WorkspaceID = @WorkspaceID

edited Sep 26, 2014 at 8:23

answered Sep 26, 2014 at 8:07

G J

5279 silver badges25 bronze badges

1 Comment

Damien_The_Unbeliever Over a year ago

NOLOCK should only be applied in specific circumstances where you've considered, carefully, whether you're happy to accept its implications. Given that it doesn't appear in the question, and there's no other mention of locking in the question, why has it suddenly appeared in your answer?

Collectives™ on Stack Overflow

How do I return a string of numbers separated by comma as an array parameter in SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related