SQL-Server: How to use IDENT_Current for SELECT statement in a scalar function

Question

Could not find any solution how to use the last inserted ID of a certain table and use this ID to select another field of the same table.

I tried this but always get syntax errors:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.sfLastCode ()

AS
BEGIN
  SET NOCOUNT ON; 

    -- Declare the return variable here
    declare @LastCode nvarchar(2)

    -- Add the T-SQL statements to compute the return value here
    @LastCode=select tblevents.CODE from tblEvents where Evid=(select IDENT_CURRENT('dbo.tblEvents'))
returns @LastCode
GO

For example I would like to get as aresult the code '123' form the record with the ID 90 . For sure the syntax is wrong at all, but I do not know where and what. I am not sure if this should be done in a SP or in a scalar function. I start the code form Access by a PT-query.

EDIT: Meanwhile I tried

select ident_Current('dbo.tblEvents') as LastEvID

and got a STRANGE result, because the number is 96, although the highest EvID is 90. 6 records have not been saved due to tests and errors in the code. Why does IDENT_Current('dbo.tblEvents') does not give me number 90?

As I read Max(EvID) is not the best choice I tried IDENT_Current('dbo.tblEvents'), but when IDENT_Current also counts not saved records it is of no use for me.

Thanks a lot Michael

Check this link http://stackoverflow.com/questions/3422168/safest-way-to-get-last-record-id-from-a-table — Dinesh Reddy Alla, Aug 07 '14 at 11:56
Why 96? Because that was the last identity value generated for that table. The next row to be inserted will get 97. You should *not* assume identity values are sequential and don't contain gaps. And `IDENT_CURRENT` is practically pointless because a) the value may not have survived the transaction, as you've observed and b) may be out of date as soon as you've obtained it due to other transactions happening at the same time. Probably, a function isn't what you need. — Damien_The_Unbeliever, Aug 07 '14 at 12:01
I think the bigger question is what are you trying to accomplish here? I have a gut feeling that you are wanting to capture the identity value of the most recently inserted row. this is often done right after the insert was made so that other related rows in other tables get the correct value for the foreign key. The best way to handle this is by using the OUTPUT clause from your insert. Anything else is a hack and will at some point end up with an incorrect value. — Sean Lange, Aug 07 '14 at 13:36

score 0 · Answer 1 · answered Aug 07 '14 at 11:58

0

Try updating your code to

SET @LastCode=(select tblevents.CODE from tblEvents where Evid=(select IDENT_CURRENT('dbo.tblEvents')))

answered Aug 07 '14 at 11:58

HotblackDesiato

350
1
8

Already tried, did not work, or better: did not bring the expected result, due to the fact which I described in my "Edit:" – mak Aug 07 '14 at 12:12

score 0 · Answer 2 · answered Aug 07 '14 at 12:07

0

Can't you just do this?:

DECLARE @LastCode VARCHAR(2)
SET @LastCode=
(
    SELECT TOP 1
        tblEvents.CODE
    FROM
        tblEvents 
    ORDER BY tblevents.EvID DESC
)
SELECT @LastCode;

answered Aug 07 '14 at 12:07

Arion

31,011
10
70
88

Thanks Arion, it works. I also have now: ALTER PROCEDURE [dbo].[spLastEvID] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT SMSCode FROM tblEvents WHERE EvID = (select max(Evid) from tblEvents) END One on both I will use. Will see if I get a different result some time. – mak Aug 07 '14 at 13:04

SQL-Server: How to use IDENT_Current for SELECT statement in a scalar function

2 Answers2