Insert new row into a table for each value in the comma separated list of values passed to a stored procedure

Question

I have a table which which looks like below, Id is the identity column.

Id | StudentId | DepartmentId | Comments | Date

I need to create a stored procedured which takes a list of StudentId's separated by a comma and inserts a new row into the table for each StudentId in the comma separated list.

I tried in the below way but that's not the right way

CREATE PROC usp_UpdateStudentAttendance
@StudentIds VARCHAR(max),
@DepartmentId INT
AS
BEGIN
INSERT INTO [dbo].[StudentAttendance] ([StudentId], [DepartmentId], [Comments], [Date])
SELECT CAST(Items AS INT), @DepartmentId, 'Attended', GETDATE() FROM dbo.splitstring(@StudentIds, ',')
END

I am getting an error 'Invalid object name 'STRING_SPLIT'

Looks like my database version is not compatible to use STRING_SPLIT , do we have any other alternative ?

Tried with below function.

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

*"I tried in the below way but that's not the right way"* Why wasn't it? We don't have the definition of `dbo.Split` so we can't run your code. Why not use the built in function `STRING_SPLIT` though? Or amend your procedure to accept a table type parameter. — Thom A, Jun 10 '21 at 10:33
"not the right way" doesn't mean anything. If you have an error, post it, if it didn't do what you expect, explain it — Nick.Mc, Jun 10 '21 at 10:36
I'm guessing you're using an old version of SQL Server then, @user187 , as `STRING_SPLIT` is in every supported version of SQL Server. Either post the definition of your function, `dbo.Split`, use a different one (such as [`DelimitedSplit8K_LEAD`](https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function), or an XML Splitter) or use a table type parameter and then you don't need a splitter at all. — Thom A, Jun 10 '21 at 10:48
Does this answer your question? [SQL Server split CSV into multiple rows](https://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows) — PankajSanwal, Jun 10 '21 at 10:56
@Larnu posted the function definition, i get an error invalid column name items — user187, Jun 10 '21 at 11:04
Get rid of that function, it's not good; it's a multi-line table value function **and** it uses iteration. Use an inline table value function like I linked to. Also, the reason you get the error "invalid column 'items'" is quite clear; your function (`dbo.split`) *doesn't* have a column called `items`. — Thom A, Jun 10 '21 at 11:12
@Larnu will do, thank u for putting me in the right direction. — user187, Jun 10 '21 at 11:25
[Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) — HABO, Jun 10 '21 at 15:31

score 0 · Answer 1 · answered Jun 10 '21 at 12:05

You are much better off using a Table Type parameter

CREATE TYPE dbo.IntList AS TABLE (val int);

GO

CREATE OR ALTER PROC usp_UpdateStudentAttendance
  @StudentIds dbo.IntList,
  @DepartmentId int
AS

INSERT INTO [dbo].[StudentAttendance]
    ([StudentId], [DepartmentId], [Comments], [Date])
SELECT val, @DepartmentId, 'Attended', GETDATE()
FROM @StudentIds;

GO

I usually have a few standard single-column table types that I use everywhere.

score 0 · Answer 2 · edited Jun 14 '21 at 15:32

CREATE OR ALTER FUNCTION [dbo].[udf_EC_SplitString]
    (
      @ipString VARCHAR(8000),
      @ipDelimiter CHAR(1)
    )
RETURNS @myTable TABLE
    (
      STRING_VALUE VARCHAR(8000)
    )
AS 
BEGIN        
    DECLARE @lvDelimeterIndex INT        
    DECLARE @lvCurrentStringValue VARCHAR(8000)        
       
    SELECT  @lvDelimeterIndex = 1        
    IF LEN(@ipString) < 1
        OR @ipString IS NULL 
        RETURN        
       
    WHILE @lvDelimeterIndex != 0        
        BEGIN   
            -- Find Delimiter Location     
            SET @lvDelimeterIndex = CHARINDEX(@ipDelimiter, @ipString)        
            -- Check Index Value
            IF @lvDelimeterIndex != 0 
            BEGIN
                -- If Delimeter Found then Find Current STRING_VALUE 
                SET @lvCurrentStringValue = LEFT(@ipString, @lvDelimeterIndex - 1)        
            END
            ELSE 
            BEGIN
                -- If Delimeter Not Found then remaining string is current STRING_VALUE
                SET @lvCurrentStringValue = @ipString        
            END           
            IF ( LEN(@lvCurrentStringValue) > 0 ) 
                INSERT  INTO @myTable VALUES  ( @lvCurrentStringValue )        
            -- Find Remaining String
            SET @ipString = RIGHT(@ipString, LEN(@ipString) - @lvDelimeterIndex)        
            -- Exit if Remaining String is Completed
            IF LEN(@ipString) = 0 
                BREAK        
        END    
    RETURN        
END

Above is the function which takes two parameter first is string separated by a delimeter and other is the delimeter character. As we are discussing for comma separted string so below call will work fine witout any issue. Please try

select * from [dbo].[udf_EC_SplitString]('India,USA,Canada',',')

score 0 · Accepted Answer · answered Jun 10 '21 at 12:54

You can try using XML for this task. Following an example:

DECLARE @DepartmentId INT = 123;
DECLARE @str NVARCHAR(100) = 'Student1, Student2, Student3';

DECLARE @t TABLE(xstr XML);
INSERT INTO @t VALUES (CAST('<students><student>' + REPLACE(@str, ', ', '</student><student>') + '</student></students>' AS XML))

SELECT T.r.value('.','varchar(100)') as student, @DepartmentId AS DepartmentId, 'Attended' AS Comments, GETDATE() AS [Date]
FROM @t
OUTER APPLY
xstr.nodes('/students/student') t(r)

Insert new row into a table for each value in the comma separated list of values passed to a stored procedure

3 Answers3