Retrieve SQL Generated Value 1

[DebbieC]

I need to retrieve a SQL generated value but I can't get it to work because I'm also returning an Identity.

Here is the stored procedure:

CREATE PROCEDURE dbo.stoAddRequest
@YearMonth int = Null,
@JurID int = Null,
@Title varchar(100) = Null,
@ReqType varchar(50) = Null,
@OriginalSubmitted bit,
@WribCode varchar(10) = Null

AS

declare @TestID int
Select @TestID = Convert(Char(6),@YearMonth) +
Right('00' + Convert(VarChar(2), IsNull(Max(Right(Convert(Char(8), TestID),2)), 0) + 1), 2)
From ReviewDraft, ServiceRequests
Where Convert(Integer, Left(Convert(Char(8), TestID),6 )) = @YearMonth


INSERT INTO ServiceRequests(TestID, JurID, Title, ReqType)
VALUES (@TestID, @JurID, @Title, @ReqType)

INSERT INTO ReviewDraft (RequestID, JurID, OriginalSubmitted, WribCode)
VALUES (@@IDENTITY, @JurID, @OriginalSubmitted, @WribCode)

Select Scope_Identity() As [Scope_Identity]
GO


This is my code to get the value of Scope_Identity but I get an error message when I try to also include the code to retrieve the TestID.


Set objCmd = objCmd.Execute

DraftID = objCmd("Scope_Identity")
sTestID = objCmd("TestID")


Anyone know what I am doing wrong?


Thanks,
Debbie

 

[DebbieC]

My code above doesn't show what I did to retrieve the TestID. Here it is in the stored procedure:


INSERT INTO ServiceRequests(TestID, JurID, Title, ReqType)
VALUES (@TestID, @JurID, @Title, @ReqType)

Select TestID From ServiceRequests Where RequestID = @@IDENTITY

INSERT INTO ReviewDraft (RequestID, JurID, OriginalSubmitted, WribCode)
VALUES (@@IDENTITY, @JurID, @OriginalSubmitted, @WribCode)

Select Scope_Identity() As [Scope_Identity]
GO

 

[JamesLean]

So you want to return the value of @testid as well as the identity value from the insert into the reviewdraft table?

INSERT ServiceRequests ...

INSERT ReviewDraft ...

SELECT SCOPE_IDENTITY() AS scope_id, @testid AS test_id

And then in your app code:

Set rs = cmd.Execute

sDraftID = rs("scope_id")
sTestID = rs("test_id")

FYI, you could also use a couple of output parameters to return your two IDs instead of using the recordset.

--James

 

[DebbieC]

Thanks! That worked! I didn't think I could do it in one Select statement since the values came from two different inserts. I guess I should have just tried it instead of assuming.

Thanks again.