Using SCOPE_IDENTITY in coldfusion...

[fdgsogc]

I'm trying to identify the ID of the column I just inserted in my MSSQL table. I've learned that I need to use SCOPE_IDENTITY function. The MSSQL server forums tell me how to use this in a stored procedure.

I would like to use it at the end of my insert query. I just can't find the proper syntax to do it.

Any ideas?

 

[ecobb]

I'm not sure if you can, I've never tried it. As a general rule, I try to always use Stored Procedures anyway. You could put your insert statement in a Procedure and just have the Procedure return the value to CF.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[fdgsogc]

Here's what I finally ended up doing. The important parts are in red.

<cfquery name="InsertServiceData" datasource="#data#">
[red]SET NOCOUNT ON[/red]
Insert into sch_cust_servicedata
(clientid, 
contactid, 
eventid, 
AssetID,
DeliveryMethod,
PickupStreet,PickupCity,PickupState,PickUpPostalCode,
)
values
('#clientid#',
'#contactid#',
'#eventid#',
'#SelectYourBoat#',
'#SelectDeliveryMethod#',
'#PickupStreet#','#PickupCity#','#PickupState#','#PickUpPostalCode#',
)
[red]SELECT SCOPE_IDENTITY() AS [lastid];
SET NOCOUNT OFF[/red]
</cfquery>

[red]<cfoutput><cfset lastid = "#insertservicedata.lastid#"></cfoutput>[/red]

 

[r937]

the INSERT has no semi-colon, but the SELECT does

does that actually work?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[fdgsogc]

I actually never use semi-colons. It existed in the code I copied from another site. The code works as posted.

 

[r937]

okay thanks

actually, it won't run, because of the dangling comma at the end of the VALUES list, but never mind that ...




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[fdgsogc]

Sorry about the dangling commas. I was shortening my query for the sake of posting less text. Forgot to trim those commas.

 

[r937]

no prob

i guess i was being overly picky



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)