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

gunakar appala

8y1.1k


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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
INSERT INTO sparsha.dbo.Genders
select Column from other table name
-1
Ravi Kiran Chanduri8y ago
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 '1,2'
-1
Rafnas T P8y ago
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 1,2
-1
Nilesh Shah8y ago
but what is the error?

try to inclose the input parameter to SP in single quotes

e.g. '1,2'
0