How to insert multiple rows in a single query using a stored

Question


CREATE PROCEDURE Usp_insertgender       
(  
@gendervalues VARCHAR(MAX)) AS   
BEGIN  INSERT INTO sparsha.dbo.Genders ([GenderName]  
)       
VALUES @gendervalues;   
  
END  
I want to execute procedure

execute Usp_insertgender (1),(2)

but am getting error plz help me

Nilesh Shah · Answer

but what is the error? try to inclose the input parameter to SP in single quotes e.g. '12'

Ravi Kiran Chanduri · Answer

Hey Gunakar you would like to send multiple values as a single string to the SP and SP should have a functionality to insert those values into the table. To do this step1: spit the string values in to multiple values (better to create a seperate function for this) step2: pass the splitted values into table -- Split function (taken from the link https://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx) CREATE FUNCTION SplitString ( @Input NVARCHAR( MAX ) @ Character CHAR (1) ) RETURNS @ Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT  @EndIndex INT SET @StartIndex = 1 IF SUBSTRING (@Input LEN(@Input) - 1 LEN(@Input)) @ Character BEGIN SET @Input = @Input + @ Character END WHILE CHARINDEX(@ Character  @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@ Character  @Input) INSERT INTO @ Output (Item) SELECT SUBSTRING (@Input @StartIndex @EndIndex - 1) SET @Input = SUBSTRING (@Input @EndIndex + 1 LEN(@Input)) END RETURN END GO --Procedure CREATE PROCEDURE Usp_insertgender (@gendervalues VARCHAR ( MAX )) AS BEGIN INSERT INTO sparsha.dbo.Genders ([GenderName]) VALUES SELECT dbo.SplitString(@gendervalues '' ); END -- Execution execute Usp_insertgender '12'

Laxmidhar Sahoo · Answer

INSERT INTO sparsha.dbo.Genders select Column from other table name

Rafnas T P · Answer

hi you have only one string parameter for this procedure but you are passing two integers. I think in your table you have two columns in GenderName table and you wanted to insert tow values to this table then create two parameter in procedure like below CREATE PROCEDURE Usp_insertgender (@gendervalue1 int @gendervalue2 int ) AS BEGIN INSERT INTO sparsha.dbo.Genders ([GenderName]) VALUES (@gendervalue1@gendervalue1); END execute Usp_insertgender 12