Can't get return value from <cfstored Proc>

[Mark Forsberg]

I am using a select statement at the end of a record insert to retreive the
identity value of the new record. This is in SQL Server 2000. The code is as
follows:

CREATE PROCEDURE dbo.InsertAccount

@dpRoleKey int

AS

Insert Into Account(DPRoleKey )
Values(@dprolekey)


Select @accountKey = Scope_Identity()

GO

I tried to retreive the @accountKey with the <cfprocresult> and no luck. I
also tried to use the <cfprocparam> with the type set to "Out". Still no luck.
Could someone point me in the right direction on how to get this value back
with <cfstoredproc>? Thanks for the assist.

Mark F

[Mark Forsberg]

I forgot to mention that I also declared the @accountKey as an Int. Sorry.

[paross1]

Perhaps try using the keyword of OUTPUT in your variable declaration.

CREATE PROCEDURE dbo.InsertAccount

@dpRoleKey int,

@accountKey int = NULL OUTPUT

AS

Insert Into Account(DPRoleKey )
Values(@dprolekey)

Select @accountKey = Scope_Identity()


Phil

[mxstu]

Mark,

To retrieve the value, either use an output parameter with <cfprocparam>
(type="OUT") as Phil has described or return the value in a resultset and use
<cfprocresult> to caputure the value. Since you are only retrieving a single
value, the output parameter method is probably more efficient.

--- not tested
CREATE PROCEDURE dbo.InsertAccount @dpRoleKey int
AS
BEGIN

Insert Into Account(DPRoleKey )
Values(@dprolekey)

Select Scope_Identity() AS accountKey

END
GO

<cfstoredproc ...>
<cfprocparam type="in" dbVarName="@dpRoleKey" ....>
<cfprocresult name="yourResultQuery">
</cfstoredproc>

<cfoutput>#yourResultQuery.accountKey#</cfoutput>