Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server database
how to pass values for Store procedure having 2 rows of arguments , via EXEC. i have this Insert statement in my Store procedure:
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 8-Sep-12 21:02pm
Edited 8-Sep-12 21:12pm
v2
Comments
Sandeep Mewara at 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ő at 9-Sep-12 3:28am
   
As I see, you want to insert the same row data twice. Why?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
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
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.
--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.
  Permalink  
Comments
Mohamed Mitwalli at 10-Sep-12 4:19am
   
5+
__TR__ at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 400
1 Maciej Los 258
2 OriginalGriff 255
3 CHill60 217
4 Sinisa Hajnal 125


Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 9 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100