Passing multiple values for same variable in stored procedure

You will need to do a couple of things to get this going, since your parameter is getting multiple values you need to create a Table Type and make your store procedure accept a parameter of that type.

Split Function Works Great when you are getting One String containing multiple values but when you are passing Multiple values you need to do something like this....

TABLE TYPE

CREATE TYPE dbo.TYPENAME AS TABLE 
 (
    arg int 
  )
 GO

Stored Procedure to Accept That Type Param

 CREATE PROCEDURE mainValues 
 @TableParam TYPENAME READONLY
 AS 
   BEGIN
    SET NOCOUNT ON;
  --Temp table to store split values
  declare @tmp_values table (
  value nvarchar(255) not null);   

   --function splitting values 
   INSERT INTO @tmp_values (value)
   SELECT arg FROM @TableParam


   SELECT * FROM @tmp_values  --<-- For testing purpose
END

EXECUTE PROC

Declare a variable of that type and populate it with your values.

 DECLARE @Table TYPENAME     --<-- Variable of this TYPE

 INSERT INTO @Table                --<-- Populating the variable 
 VALUES (331),(222),(876),(932)

EXECUTE mainValues @Table   --<-- Stored Procedure Executed 

Result

╔═══════╗
║ value ║
╠═══════╣
║   331 ║
║   222 ║
║   876 ║
║   932 ║
╚═══════╝

Your stored procedure is designed to accept a single parameter, Arg1List. You can't pass 4 parameters to a procedure that only accepts one.

To make it work, the code that calls your procedure will need to concatenate your parameters into a single string of no more than 3000 characters and pass it in as a single parameter.

Well, without a bigger view of your exact setup, how this is being called and what is happening in f_split it can be hard to debug the situation as I am basically just guessing. I can, however, offer an alternative solution that doesn't involve UDFs but rather relies on SQL Server's build in mechanisms... XML.

The below proc declaration accepts an XML type parameter called @parmsXML. This would replace your @Arg1List parameter. When you EXEC dbo.mainValues, you supply the @parmsXML parameter as a string of XML nodes rather than a comma separated list (I assume this is what you are doing but from your question it is not exactly clear):

<parms>
    <parm>331</parm>
    <parm>222</parm>
    <parm>876</parm>
    <parm>932</parm>
</parms>

The stored proc does this:

1. Just selects the values from from the @parmsXML variable directly
2. SELECTs the values from the @parmsXML variable into a temp #t_values table
3. SELECTs from the #t_values table

In your own implementation, you could get rid of the first step (Just select....), then change the SELECT INTO into a INSERT INTO SELECT.

I have the below script setup so that it will DROP IF EXISTS then CREATE the proc and then EXECUTE it with the @parmsXML setup as above.

--========================================================================================================================
/* DROP AND RECREATE PROC                                                                                               */
--========================================================================================================================

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'mainValues' 
)
   DROP PROCEDURE dbo.mainValues
GO

CREATE PROCEDURE dbo.mainValues
    @parmsXML XML
AS

--========================================================================================================================
/* INTERPRETER DIRECTIVES                                                                                               */
--========================================================================================================================

SET NOCOUNT ON;    -- Turn off "(N row(s) affected)" messages
SET XACT_ABORT ON; -- Auto ROLLBACK on exception

--========================================================================================================================
/* PARMS PROCESSING                                                                                                     */
--========================================================================================================================

-- select from @parmsXML

RAISERROR('Selecting values directly from @parmsXML', 0, 1);

SELECT Parm = n.x.value('.[1]', 'INT')
FROM @parmsXML.nodes('/parms[1]/parm') n(x)
;

-- insert into

RAISERROR('Inserting @parmsXML values into #t_values', 0, 1);

SELECT Parm = n.x.value('.[1]', 'INT')
INTO #t_values
FROM @parmsXML.nodes('/parms[1]/parm') n(x)
;

-- select from #t_values

RAISERROR('Selecting from #t_values', 0, 1);

SELECT * 
FROM #t_values
;


GO

--========================================================================================================================
/* Example EXEC code runs stored proc with @parmsXML supplied                                                           */
--========================================================================================================================

EXECUTE dbo.mainValues @parmsXML = '
    <parms>
        <parm>331</parm>
        <parm>222</parm>
        <parm>876</parm>
        <parm>932</parm>
    </parms>
    '
GO