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
.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…