Pass a list of integers to stored procedure and perform insert on each

[robertx]

Is there a way to pass a list of integers to a stored procedure and perform an insert query on each? I have tried running a SELECT query to return the integer list within the stored procedure but it fails with:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have also tried passing a list as a table-valued parameter but the insert query also failed.

Is this possible and if so, what is the SQL syntax to achieve it?

[Zvoni]

Which DBMS?

[robertx]

Which DBMS?

MSSQL Server

[Zvoni]

https://stackoverflow.com/questions/...ored-procedure

[Zvoni]

I think the "easiest" way would be to concatenate/join the integers into a string (1 2 3 4 5 becomes '1,2,3,4,5', comma delimiter) so you actually pass a single string into the SP.
Inside the SP you split it up again, and do your inserts in a loop

[robertx]

https://stackoverflow.com/questions/...ored-procedure

I know how to pass a list to a stored procedure. How can I iterate it and perform an insert query with each value?

[Zvoni]

Here is an example how to INSERT with a loop. Just put the loop-logic in your SP
https://www.sqlshack.com/sql-while-l...in-sql-server/

[robertx]

Here is an example how to INSERT with a loop. Just put the loop-logic in your SP
https://www.sqlshack.com/sql-while-l...in-sql-server/

Is there a way of looping through a table-valued parameter and assigning each value to a variable so that it can be passed to an insert query?

[wqweto]

You can use string_split system table-valued function to split a VARCHAR on a delimeter.

For iterating the list you have to use a cursor which is suboptimal and probably speaks about wrong design about accomplishing the task in first place

DECLARE     @Param1     VARCHAR(1000) = 'This is a test'
            , @Param2   VARCHAR(50) = '42'

DECLARE     @CrsList    CURSOR
            , @IterValue VARCHAR(50)

SET         @CrsList = CURSOR FAST_FORWARD FOR
SELECT      LEFT(value, 50)
FROM        string_split(@Param1, ' ')

OPEN        @CrsList

WHILE       1=1
BEGIN
            FETCH NEXT
            FROM        @CrsList
            INTO        @IterValue

            IF          @@FETCH_STATUS <> 0 BREAK

            PRINT       'EXEC dbo.MyStoredProc ''' +  @IterValue + ''', ''' + @Param2 + ''''
END

CLOSE       @CrsList
DEALLOCATE  @CrsList

cheers,
</wqw>

[robertx]

You can use string_split system table-valued function to split a VARCHAR on a delimeter.

For iterating the list you have to use a cursor which is suboptimal and probably speaks about wrong design about accomplishing the task in first place

DECLARE     @Param1     VARCHAR(1000) = 'This is a test'
            , @Param2   VARCHAR(50) = '42'

DECLARE     @CrsList    CURSOR
            , @IterValue VARCHAR(50)

SET         @CrsList = CURSOR FAST_FORWARD FOR
SELECT      LEFT(value, 50)
FROM        string_split(@Param1, ' ')

OPEN        @CrsList

WHILE       1=1
BEGIN
            FETCH NEXT
            FROM        @CrsList
            INTO        @IterValue

            IF          @@FETCH_STATUS <> 0 BREAK

            PRINT       'EXEC dbo.MyStoredProc ''' +  @IterValue + ''', ''' + @Param2 + ''''
END

CLOSE       @CrsList
DEALLOCATE  @CrsList

cheers,
</wqw>

I believe string_split wasn’t introduced until SQL Server 2016 and I need to be able to support 2012 and 2014. Is there an alternative method that is supported by these versions?

[Zvoni]

https://stackoverflow.com/questions/...l-split-string

[wqweto]

I believe string_split wasn’t introduced until SQL Server 2016 and I need to be able to support 2012 and 2014.

Not immediately obvious in OP. Yes, there are alternative custom table-valued functions.

I'm using one that performs adequately on sql2000 onwards but uses our custom conditionals on applying to client databases.

Don't have time to clean it up so pasting it as it is -- you might find sql2008 version compatible with your target versions.

It also returns a second SeqNo column with value's position in the original list (which string_split glaringly misses)

cheers,
</wqw>

[GaryMazzone]

This works for 2012 or 2014 (and 2008,2008Rs, 2005)

CREATE FUNCTION usp_fnsplit (
	@inputVals VARCHAR(MAX),
	@delimiter CHAR(1)
)
RETURNS @output TABLE (splitval VARCHAR(MAX))
BEGIN
	DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @inputVals) 
    WHILE @start < LEN(@inputVals) + 1 
		BEGIN 
			IF @end = 0  
				SET @end = LEN(@inputVals) + 1
       
			INSERT INTO @output (splitval)  
			VALUES(SUBSTRING(@inputVals, @start, @end - @start)) 
			SET @start = @end + 1 
			SET @end = CHARINDEX(@delimiter, @inputVals, @start)
        
		END 
	RETURN
END

[robertx]

https://stackoverflow.com/questions/...l-split-string

Thanks this led me to this solution which was a bit clearer for my situation:

https://www.admfactory.com/split-a-s...in-sql-server/