How to insert comma-separated string value in stored procedure

Question

This is my stored procedure to add multiple players:

CREATE PROCEDURE [dbo].[AddMultipleUserofSameCountry]    
    @UserIds NVARCHAR(MAX),       
    @CountryCode INT,    
    @OwnsHasAccess INT,   
    @QryResult INT OUTPUT    
AS    
BEGIN TRY    
    SET @QryResult  = 0;    

    BEGIN TRANSACTION    
    BEGIN  
        INSERT INTO Players (CountryCode, UserId, OwnsHasAccess, InsertedWhen) 
        VALUES (@CountryCode,
                (SELECT Value FROM dbo.SplitString(@UserIds, ',')),
                @OwnsHasAccess, GETDATE());  
    END    

    COMMIT TRANSACTION    

    SET @QryResult  = 1;    
END TRY    
BEGIN CATCH    
    ROLLBACK TRANSACTION    
    SET @QryResult  = 0;    
END CATCH

UserIds is comma-separated string value

DAL Code

array = string.Join(",", UserIds.Select(n => n.ToString()).ToArray());

SqlParameter sqlParam = cmdProc.Parameters.AddWithValue("@UserIds", array);
sqlParam.SqlDbType = SqlDbType.VarChar;

Please suggest is there any way to pass multple UserId to insert multiple players

You used the MySQL tag, yet the code looks more like being for SQL Server. Please check the tags and correct them if necessary. Please **also** add the tag for the specific version of your DBMS. — sticky bit, Aug 09 '18 at 11:00
Is there a reason for not calling the procedure for every id? Does it have to be done all by one? — Cataklysim, Aug 09 '18 at 11:05

score 2 · Answer 1 · answered Aug 09 '18 at 11:06

You should build complete rows to use in the INSERT statement:

CREATE PROCEDURE [dbo].[AddMultipleUserofSameCountry]    
 @UserIds nvarchar(MAX),       
 @CountryCode int,    
 @OwnsHasAccess int,   
 @QryResult int output    
AS    
 Begin Try    
  Set @QryResult  = 0;    
  Begin Transaction    
   Begin  
       INSERT INTO Players(CountryCode,UserId,OwnsHasAccess,InsertedWhen)
       SELECT @CountryCode, Value, @OwnsHasAccess, GETDATE()
          FROM dbo.SplitString(@UserIds,',');  
   END    

   Commit Transaction    
   Set @QryResult  = 1;    

 End Try    
 Begin Catch    
  Rollback Transaction    
  Set @QryResult  = 0;    
 End Catch

Note: I am assuming dbo.SplitString works the same as STRING_SPLIT in SQL Server 2016

Here is [an SO article discussing a SQL Server SplitString implementation](https://stackoverflow.com/questions/21428612/splitting-the-string-in-sql-server). — Brian, Aug 09 '18 at 15:25

How to insert comma-separated string value in stored procedure

1 Answers1