Mik Muller wrote: > For years I've been using SELECT @@IDENTITY AS ID in my code to get the > record ID for a newly inserted record. And now I'm wondering... would this, > or something similar, work inside an insert statement? > > I'm working on some insert code right now and one of the fields is a notes > field that I'm storing some HTML, including (possibly) a path to an image > that would be based on the record ID, ie; /path/to/###/record/image.jpg > > I know it's a bit redundant, and that I could make an image field in the > table and automate the appearance of the image through code, but I was > wondering if there was anything in the SQL insert statement that would be > aware of the impending unique ID that I could use. > > Mik > > This may not be the most efficient way, but you could use a stored procedure to add the record without the notes field, grab the ID of the newly created record, then update it with the appropriate info for the notes field.
Incidentally, you should look at using scope_identity() instead of @@identity. @@identity does not always return the ID of the record you just added, particularly if there is a trigger on that table. It could return the ID of a record that was added by the trigger instead of your actual insert statement. In most cases you would not need to worry about that, but at some point you or somebody else may add a trigger to that table for whatever reason and you will be getting the incorrect ID returned to you. I just stick with scope_identity() from the beginning and never have to worry about it. Steve --- --- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273015 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
