如何通过EXEC传递具有两行参数的存储过程的值? [英] how to pass values for Store procedure having 2 rows of arguments , via EXEC ?

问题描述

如何通过EXEC传递具有两行参数的存储过程的值.我的存储过程中有此插入语句:

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)

现在如何传递值?请仔细查看我的SP. PLZ 请.

我正在使用sql server 2008

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

I m using sql server 2008

推荐答案

您的问题不清楚.我假设您有多个要通过存储过程插入到数据库中的学术记录.
我能想到的您想要通过将记录作为值传递到存储过程中来插入记录的唯一原因是，因为存储过程会将数据插入到多个表中.
这是一个示例方法.

让我们考虑一个示例，该示例要求将多个用户插入数据库.
这是用户表

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', '[e-mail]' UNION ALL
SELECT 'CPike', 'Christopher', 'Pike', '[e-mail]'

然后，我们从临时表中读取每一行，并将其循环传递到存储过程，如下所示

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.

这篇关于如何通过EXEC传递具有两行参数的存储过程的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！