How to pass more than one char as a variable in a stored procedure?

Question

I've created the following stored procedure:

ALTER PROCEDURE [dbo].[CountInJunction]
        @Mod  as nvarchar(10),
        @Junction as nvarchar(10),
        @PJ as nvarchar(10),
        **@case as varchar(10)**,
        @Date as varchar(20)
    as

begin

declare @result as int

select @result = count(distinct CONCAT ([UCID],[CALLSEGMENT])) 

from IVR_LINES
where MODULE = @Mod and  DATE = @date
and EVENT_NAME = @Junction and **EVENT_VALUE in (@case)** 

insert into [dbo].[MainJuncTable] values(@Mod,@PJ,@Junction,@case,@result,null,null,@date)

return @result

end

I would like to pass ('0','5') as @case.

for some reason, I get 0 as a result, which is not correct. Its seems that the SP doesn't interpret ('0','5') correctly. I've been trying multiple combinations such as:

'0','5'

'0'+','+5''

'0,5'

etc..

nothing works.

Is there any way I can pass these chars correctly?

Thanks.

See this nice [article by Erland Sommarskog](http://www.sommarskog.se/arrays-in-sql.html) — Andomar, Sep 21 '16 at 08:13

score 0 · Accepted Answer · answered Sep 21 '16 at 08:29

Send the values as a single string like ('0,5')

Then in where condition u need to split and select the values like,

where EVENT_VALUE in (select val from Split(@case,','))

Split is user defined function,you need to create before using it.

CREATE FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

GO

score 0 · Answer 2 · edited May 23 '17 at 12:08

In every case, use this as your parameter value: '0,5'

But how to use it depends on the version of sql server you're using.

If you've got 2016, there's STRING_SPLIT. https://msdn.microsoft.com/en-us/library/mt684588.aspx

If you don't have it, you can create a function. See related stackoverflow posts: How to split a comma-separated value to columns Or if you want rows: SQL query to split column data into rows

(See the higher rated recommendations in both of those.)

How to pass more than one char as a variable in a stored procedure?

2 Answers2