CREATE PROCEDURE Usp_insertgender
(@gendervalues VARCHAR(MAX))
AS
BEGIN
INSERT INTO sparsha.dbo.Genders ([GenderName])
VALUES @gendervalues;
END
I tried above procedure but I am getting an error
Msg 102, Level 15, State 1, Procedure Usp_insertgender, Line 10
Incorrect syntax near '@gendervalues'.
I am passing @gendervalues values as ('1'),('2')
Probably the easiest way is to define TVP:
CREATE TYPE myTVPType AS TABLE( v VARCHAR(50));
GO
CREATE PROCEDURE Usp_insertgender (@gendervalues myTVPType READONLY)
AS
BEGIN
INSERT INTO sparsha.dbo.Genders ([GenderName])
SELECT v
FROM @gendervalues;
END;
How to call it:
DECLARE @tvp AS myTVPType;
INSERT INTO @TVP (v)
VALUES ('1'),('2');
EXEC Usp_insertgender @TVP;
GO
by giving parameter as ('1'),('2') The only way I see is dynamic SQL but it will be huge antipattern to present it here.Try this: (with dynamic SQL)
CREATE PROCEDURE Usp_insertgender
(@gendervalues VARCHAR(MAX))
AS
BEGIN
DECLARE @sql nvarchar(2000)
SET @sql = N'INSERT INTO sparsha.dbo.Genders ([GenderName])
VALUES ' + @gendervalues
EXEC sp_executesql @sql
END
