August 24, 2005 at 9:58 am
i am working on a ColdFusion app where i add a record to a table and then return to a page where i list info from records in the table. i would like to highlight the record that was just added in red.
so... the table has an ID column which is an identity column so it auto increments. so without doign another query right after the insert query is there some way for my app to access the ID for the record i just inserted?
August 24, 2005 at 10:08 am
That information is kept in the Scope_Identity() function.
You can send back the information with an output parameter (preffered) or the return >>
Select @NewId = SCOPE_IDENTITY()
August 24, 2005 at 10:13 am
ok so if my select query for the resulting page looked like this:
SELECT *
FROM tblName
ORDER BY Lname
what would my new query look like? something like this?
SELECT @NewId = SCOPE_IDENTITY() ,*
FROM tblName
ORDER BY Lname
August 24, 2005 at 10:16 am
Aren't you using a stored proc to insert the new data?
August 24, 2005 at 10:19 am
no i am not. should i be?
August 24, 2005 at 11:27 am
You should ALWAYS (99.9999%) use SP.
Here's an exemple :
CREATE PROCEDURE [dbo].[AjouterDB] @DbName as varchar(50), @CNStr as varchar(300), @FkServer as int
AS
SET NOCOUNT ON
insert into dbo.Databases (DbName, CnStr, FkServer) values (@DbName, @CNStr, @FkServer)
return SCOPE_IDENTITY()
SET NOCOUNT OFF
GO
August 24, 2005 at 1:06 pm
i know the advantage is that the performace is better but for queries that are often used but if i had a stored procedure on my SQL server for evey query my SQL server woudl be littered with hundreds of SP's.
plus i can see using a SP in a case where performance is an issue but if it is nto then there seems to be no need for a SP.
unless there is another advantage to SP's that i am unaware of (other than perromance)
August 24, 2005 at 1:09 pm
August 24, 2005 at 1:12 pm
interesting article - (i have only skimmed it so far but will read it in its entirety shortly) looks like that answers some of my questions - thanks for the info!
August 24, 2005 at 1:19 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply