Stack Exchange
How can I pass a set of static as well as list of column data into a stored procedure at the same time?
My Stored procedure is 
   ALTER PROCEDURE [dbo].[Register]
    @Id        int,
    @Name nvarchar(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.Group (Id, Name) 
        VALUES(@Id, @Name)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END
GO

I want pass like this data for insert into this table
@Id = 1 
@Name = 'test1,test2,test3,test4,test5'

and result like this
Id   Name
1    test1
1    test2
1    test3
1    test4
1    test5

Kindly share the best possible way to achieve this.

In this case you either use a user-specified tabular data type and pass a table to your store procedure or you can split the string from single varchar parameter on commas.

You could make use of TVPs:

Create a User Defined Table Type
CREATE TYPE [dbo].[TestTableType] AS TABLE(
                                            Id [int] NOT NULL,
                                            Name varchar(10)
                                          )

Pass it as a parameter to your stored procedure 
ALTER PROCEDURE [dbo].[Register]
    @testTable TestTableType READONLY
AS
BEGIN
BEGIN TRY
    INSERT INTO dbo.Group (Id, Name) 
    SELECT *
    FROM @testTable

    SELECT 0
END TRY
BEGIN CATCH
    SELECT -1
END CATCH
END


You could find more information in the documentation and please note that the TVP, when used as a parameter to a stored procedure has to be declared as READONLY.

SQL Server 2016 offers string_split(), which does exactly what you want to do:
ALTER PROCEDURE [dbo].[Register] (
    @Id   int,
    @Name nvarchar(max)
) AS
BEGIN
    BEGIN TRY
        WITH s AS (
            SELECT STRING
        INSERT INTO dbo.Group (Id, Name) 
            VALUES(@Id, @Name)
            SELECT i.id, s.val
            FROM (SELECT @id as id) s CROSS APPLY
                 STRING_SPLIT(name, ',') s(val)

        SELECT 0
    END TRY
    BEGIN CATCH
        SELECT -1
    END CATCH
END;
GO

For earlier versions of SQL Server you can find code for string_split() on the web.

First create yourself a split function, like this:
create FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(max) -- List of delimited items
  , @sDelimiter VARCHAR(5) -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(max))

BEGIN
DECLARE @sItem VARCHAR(max)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

You will then be able to do something along these lines:
declare @test table
(
id int,
name varchar(20)
)

declare @list varchar(MAX) = 'test1,test2,test3,test4,test5'
declare @id int = 1

INSERT INTO @test SELECT @id, * from dbo.fnSplit(@list,',')

SELECT * FROM @test

This returns 
1   test1
1   test2
1   test3
1   test4
1   test5

Obviously you will not need the select, but I just wanted to show that the insert worked!

Please don't use this splitter. It is horrific for performance. It is a multi-statement table valued function with  a loop inside. Here are several splitters that are much better. https://sqlperformance.com/2012/07/t-sql-queries/split-strings
