Insert multiple rows with single dynamic data-CodePudding

I'm trying to insert same data for multiple rows with different @EmpId into table.

Query:

CREATE PROCEDURE usp_SaveEmployee
    (@EmpIds varchar(50),  //1,2,3,4
     @StateId int,
     @CountryId int,
     @Comments varchar(50))
AS
BEGIN
    INSERT INTO dbo.tblEmpDetails (EmpId, StateId, CountryId, Comments)
        SELECT * 
        FROM [dbo].[FN_ListToTable] (',',@EmpIds)

    -- How can I add this: (@StateId, @CountryId, @Comments)
END

Or is there any other better way to handle this?

CodePudding user response：

Simply define the correct columns in the select list:

INSERT INTO dbo.tblEmpDetails (EmpId, StateId, CountryId, Comments)
SELECT [<ReturnedColumn>], @StateId, @CountryId, @Comments
from dbo.FN_ListToTable (',', @EmpIds);

Also note that SQL Server has a built-in string_split() function.