sql server - Insert string's each values into a single column

Question

Here I want to insert the complete string each values into a column. For which I have written the following script:

Example:

Table: test

create table test
(
cola varchar(10),
colb varchar(max),
colc varchar(10)
);

Note: Now I want to insert records like the following by calling stored procedure:

cola   colb   colc
------------------
X1     M1     Z1
X1     M2     Z1
X1     M3     Z1
X1     M4     Z1

Stored Procedure: sptest

CREATE PROC sptest
@cola varchar(10),
@colb varchar(max),
@colc varchar(10)

AS

Declare @dynamic varchar(max)

    SET @dynamic =N'delete from test where colc='''+ @colc +'''';
    PRINT(@dynamic)
    EXEC(@dynamic)

    SET @dynamic =N'insert into test values('''+@cola+''','''+@colb+''','''+@colc+''')';
    PRINT(@dynamic)
    EXEC(@dynamic)

GO

Note: First I need to delete the records by check with the colc values and after that insert the records.

Calling Function:

EXEC sptest
@cola = 'X1',
@colb = 'M1,M2,M3,M4',
@colc = 'Z1'

Note: In the calling function as shown above the colb values must insert as shown in the above table. I am not getting how to insert the complete string each values in the column colb.

Answer 1

SQL does not have any mechanism for automatically splitting a string of comma-separated values (your @colb-variable into multiple inserts. You will need to write some code to do this splitting yourself.

Basically, you should do something like this:

1. In a while loop, use CHARINDEX to determine the position of the next , in @colb. Store this position in a variable.
2. Use SUBSTRING to retrieve only the characters from @colb up to the position stored in (1).
3. Call INSERT with @cola, @colc and the value you extracted in (2).
4. Repeat until no more , found in @colc.