Search and do multiple inserts via stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Search and do multiple inserts via stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-03 : 07:32:05

Bharti writes "Problem definition: Search and do multiple inserts
please check my stored procedure...

Two Tables.

Table 1: Users has the following fields
Fieldnames PartyIdentifier
ADHUserName
ADHJobID

Table 2: userssystems has the following fields
Fieldnames PartyIdentifier
ADHSystemID
ADHSystemFormID
RoleID

likely sequence of steps 2.
SEARCH
1.Find the partyidentifier in the users table with ADHJobID 3
a.select PartyIdentifier,ADHJobId from Users
where ADHJobID = @ADHJobID
need to store partyidentifier’s. in an array
-----------------------------------------------------------
Do Multiple INSERT ‘s
2. I need to add the searched party identifier from the previous step and associate them w/the following fields:
a. Party Identifier (PI) (return partyidentifier from above)
b. System Id (SI)
c. Form ID (FI)
d. Role ID (RI)
-------------------------------------------------------------
Illustrative Example
Lets assume Step one results as follows.
Partyidentifier ADHJobID
100 3
101 3
102 3

then associate the data with the Party identifier 101,100,102 in the table in usersystems
PI SI FI RI
100 1 2 3
101 1 2 3
101 1 2 3

Please PLEASE ..see what am i doing wrong...
CREATE PROCEDURE sp_insertmultiple
(
@ADHJobID int,
@ADHSystemId int,
@ADHSystemFormsId int,
@RoleId int
)
AS
SELECT PartyIdentifier, ADHJobID From Users
where ADHJobID =@ADHJobID
return @@identity

SELECT @@IDENTITY AS 'PartyIdentifier'
INSERT INTO mytest(PartyIdentifier, ADHSystemId, ADHSystemFormsId, RoleId)
VALUES (@@identity,@ADHSystemId, @ADHSystemFormsId, @RoleId)
GO"

dsdeming

479 Posts

Posted - 2003-07-03 : 09:07:50

From BOL:

quote:
@@IDENTITY
Returns the last-inserted identity value.

There are a couple of problems here, but the show stopper is "return @@identity". This will exit the stored procedure immediately and return the last-inserted identity value, which I don't think is what you're after.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources