|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-21 : 11:58:56
|
Michael writes "I want to return 2 identities from 2 separate inserts in the same sproc (as one recordset, because I can't figure out how to deal with multiple recordsets from the same sproc in ASP). I came up with the solution below, using a temp table, but it seems there must be an easier way?
CREATE PROCEDURE sproc_AddContact
/* begin params for tblContact */
@Salutation INT,
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Company VARCHAR(50),
@IsEmployee BIT,
@IsClient BIT,
@IsVendor BIT,
@Notes VARCHAR(1024),
/* end params for tblContact */
/* begin params for tblContactLocation */
@Address1 VARCHAR(255),
@Address2 VARCHAR(255),
@City VARCHAR(50),
@State INT,
@Zip VARCHAR(50),
@Country INT,
@Email VARCHAR(255),
@URL VARCHAR(255)
/* end params for tblContactLocation */
AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblContact
(
FK_SalutationID,
ContactFirstName,
ContactLastName,
ContactCompany,
ContactIsEmployee,
ContactIsClient,
ContactIsVendor,
ContactNotes
)
VALUES
(
@Salutation,
@FirstName,
@LastName,
@Company,
@IsEmployee,
@IsClient,
@IsVendor,
@Notes
)
DECLARE @ContactID INT
SET @ContactID = @@IDENTITY
INSERT INTO tblContactLocation
(
ContactLocationAddress1,
ContactLocationAddress2,
ContactLocationCity,
FK_StateID,
ContactLocationZip,
FK_CountryID,
ContactLocationEmail,
ContactLocationURL,
FK_ContactID
)
VALUES
(
@Address1,
@Address2,
@City,
@State,
@Zip,
@Country,
@Email,
@URL,
@ContactID
)
SET NOCOUNT OFF
DECLARE @LocationID INT
SET @LocationID = @@IDENTITY
CREATE TABLE #tbltemp
(ContactID int,
LocationID int)
INSERT INTO #tbltemp VALUES(@ContactID, @LocationID)
SELECT ContactID, LocationID FROM #tbltemp
DROP TABLE #tbltemp
END " |
|
Returning ID's from multiple inserts