Need query to pass list to stored procedure in SQL Server

Question

I am new to sql server and need help to creating a query to insert all data into table in one call using stored procedure. For example I have 1 User(say "Tech" has Id 1) and list of Customers("C#","ASP","MVC").
My Table structure Id (PK),
UserId(FK)
Customers varchar.

Accepted result like
UserId----|-----Customers
1 ----------|----- C#
1 ----------|----- ASP
1 ----------|----- MVC

I will pass userId and list of customers to my SP.

What is your question, you just told us your requirements, you have not stated what the problem you are having implementing this by yourself. — Scott Chamberlain, Feb 17 '17 at 18:48

score 1 · Accepted Answer · answered Feb 17 '17 at 20:03

One way of doing this is as follows . I am assuming that you have input like this format @userId=1 , @ListOfSubjects ='asp,c#' .. here is the sample proc . The idea is to first convert list od comma seprated string ofsubject to table and then insert it . To do that I created a sample UDF which will retun a table when comma separated string is passed to it .

        create proc SampleUser_Insert 
         @UserId int , 
        @ListOfSubjects varchar(1000)
        as
        begin

             --set @UserId=1
             --set @ListOfSubjects ='asp,c#,mvc'


             declare @sampleTable table (userId int, subjects varchar(100))

             insert into    @sampleTable (userId,subjects )

             SELECT @userId, data FROM dbo.[SplitString](@ListOfSubjects, ',')
             select * from @sampleTable

        end

    --  exec SampleUser_Insert 1,'a,b,c'

the udf is as follows

    CREATE FUNCTION [dbo].[SplitString]
    (
        @String NVARCHAR(4000),
        @Delimiter NCHAR(1)
    )
    RETURNS TABLE
    AS
    RETURN
    (
        WITH Split(stpos,endpos)
        AS(
            SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
            UNION ALL
            SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
                FROM Split
                WHERE endpos > 0
        )
        SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
            'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
        FROM Split
    )
    GO

@Kida welcome happy coding – Yashveer Singh Feb 19 '17 at 08:25 — Yashveer Singh, Feb 19 '17 at 08:25

Need query to pass list to stored procedure in SQL Server

1 Answers1