Click here to Skip to main content
15,888,908 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to pass values for Store procedure having 2 rows of arguments , via EXEC. i have this Insert statement in my Store procedure:
SQL
insert into tblAcademicRecord(educationlevel, examname,institution,passingyear,obtainedmarks,totalmarks,grade, percentage, remarks, userid)
    values(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid),(@educlvl, @examname, @institution, @Passingyr, @obtainedmarks, @totalmarks, @grade, @percentage, @remarks, @userid)

now how to pass values ? have a close look at my SP. PLZPlease.

I m using sql server 2008
Posted
Updated 8-Sep-12 21:12pm
v2
Comments
Sandeep Mewara 9-Sep-12 3:13am    
Not clear.

1. You just share an insert query, where is SP?
2. What do you mean by 'two rows of arguments'? Count?
Zoltán Zörgő 9-Sep-12 3:28am    
As I see, you want to insert the same row data twice. Why?

1 solution

You question is not clear. I am assuming you have more than one Academic Record that you want to insert into to your database through a stored procedure.
The only reason I can think of you wanting to insert the records by passing it as values into the stored procedure is because the stored procedure inserts data into multiple tables.
Here is a sample approach.

Lets consider an example where the requirement is to insert multiple users into the database.
This is the user table
SQL
CREATE TABLE tblUser
(
	UserID INT IDENTITY(1,1),
	UserName VARCHAR(50),
	FirstName VARCHAR(50),
	LastName VARCHAR(50),
	Email VARCHAR(50)
)


This is the stored procedure that inserts data into user table
SQL
CREATE PROCEDURE usp_CreateUser
@UserName VARCHAR(50),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Email VARCHAR(50)

AS

BEGIN

	INSERT INTO tblUser
	(
		UserName,
		FirstName,
		LastName,
		Email
	)
	VALUES
	(
		@UserName,
		@FirstName,
		@LastName,
		@Email
	)


END


First we create a temp table that holds all the users we want to insert as shown below.
SQL
--Create temp table
DECLARE @TempUserList TABLE
(
	ID INT IDENTITY(1,1),
	UserName VARCHAR(50),
	FirstName VARCHAR(50),
	LastName VARCHAR(50),
	Email VARCHAR(50)
)

--Insert users into temp table
INSERT INTO @TempUserList
SELECT 'chuckynoris', 'Chuck', 'Norris', 'chuckynoris@somedomain.com' UNION ALL
SELECT 'CPike', 'Christopher', 'Pike', 'CPike@somedomain.com'


Then we read each row from temp table and pass it to the stored procedure in a loop as shown below
DECLARE @Count INT	--Used to get the present Row ID
DECLARE @MaxCount INT --Used to hold the maximum number of rows in the temp table

SELECT @Count = 1, @MaxCount = COUNT(*) FROM @TempUserList


--These variables will hold the values that will be passed into the stored procedure.
DECLARE @UserName VARCHAR(50),
	@FirstName VARCHAR(50),
	@LastName VARCHAR(50),
	@Email VARCHAR(50)



WHILE @Count <= @MaxCount
BEGIN

	SELECT @UserName = UserName, @FirstName = FirstName, @LastName = LastName, @Email = Email FROM @TempUserList WHERE ID = @Count

	EXEC usp_CreateUser @UserName, @FirstName,  @LastName, @Email

	SET @Count = @Count + 1

END

SELECT * FROM tblUser


Hope this helps.
 
Share this answer
 
Comments
Mohamed Mitwalli 10-Sep-12 4:19am    
5+
__TR__ 10-Sep-12 4:24am    
Thanks Mohamed :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900