How do I use IDENT_CURRENT?

Good afternoo!

I need to get the value of the PrimaryKey of the last record entered into a table, without inserting a record. I have tried wriring a stored procedure to do this with 0 success. (you will laugh I am sure!). I am using the result on a web page that is receiving info from the same table. Can I add IDENT_CURRENT into the stored procedure of another querry to get the value?

 CREATE Procedure spGetLastReturnIDFromCMRCRGA
(
@lastreturnID int output
)
AS
SELECT
    @lastReturnID = IDENT_CURRENT
FROM CMRC_Rga
GO
dplsrAsked:
Who is Participating?
 
JaffaKREEConnect With a Mentor Commented:
This might be useful to you:

IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Try:

CREATE PROCEDURE pGetLastReturnIDFromCMRCRGA ( @MyIdent INT )
as
BEGIN
 DECLARE @MyIdent INT
 Select @MyIdent = Select @@IDENTITY
 RETURN @MyIdent
END
0
 
xxg4813Commented:

Try this:

select IDENT_CURRENT('table_name')


Good luck
0
 
dplsrAuthor Commented:
Hi JaffaKree

I changed your sp around to get rid of a couple of errors. I tessted it in the QueryAnalyzer and it returns null.
and
  on the webpage I get an error: ystem.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spGetLastReturnIDFromCMRCRGA

CREATE PROCEDURE spGetLastReturnIDFromCMRCRGA (@MyIdent INT output)
as
BEGIN

 Select  @MyIdent = @@IDENTITY
FROM CMRC_RGA
 RETURN @MyIdent
END
GO
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JaffaKREECommented:
you'll get a null if no identity values have been inserted in this session.

try changing it to

select IDENT_CURRENT (TableName)



0
 
dplsrAuthor Commented:
Ok success with the Anyalyzer! with:
CREATE PROCEDURE spGetLastReturnIDFromCMRCRGA (@myident int output)
as
BEGIN

 Select @myident =  IDENT_CURRENT('CMRC_RGA')

return  @myident
END
GO

but i am still getting an error: CREATE PROCEDURE spGetLastReturnIDFromCMRCRGA (@myident int output)
as
BEGIN

 Select @myident =  IDENT_CURRENT('CMRC_RGA')

return  @myident
END
GO
0
 
dplsrAuthor Commented:
i am still getting error:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spGetLastReturnIDFromCMRCRGA'

could the name be to long? i'll check
0
 
JaffaKREECommented:
How is this being called, through ASP ?
0
 
dplsrAuthor Commented:
.net

webpage code:
 Dim getlastid As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim lastreturnID as string = getlastId.GetLastReturnIDFromCMRCRGA

and

.vb business object

Public Function  GetLastReturnIDFromCMRCRGA()            
                        Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim myCommand As New SqlCommand(" spGetLastReturnID", myConnection)      
                        Dim parameterlastReturnID As New SqlParameter("@lastReturnID", SqlDbType.Int, 4)
            parameterlastReturnID.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(parameterlastReturnID)                  
                                  myConnection.Open()
                myCommand.ExecuteNonQuery()      
                         myConnection.Close()
                               Dim lastreturnID As Integer = CInt(parameterlastreturnID.Value)

                Return lastreturnID.ToString()
                        
            end Function
0
 
dplsrAuthor Commented:
wwops got to change some variables
0
 
dplsrAuthor Commented:
i changed the parameters etc to myindent, but still get the System.Data.SqlClient.SqlException  error
            
            
0
 
JaffaKREECommented:
Hmm, not really familiar with .net... shouldn't it be INT though ?
0
 
dplsrAuthor Commented:
INT where?
0
 
JaffaKREECommented:
Dim lastreturnID as string = getlastId.GetLastReturnIDFromCMRCRGA
0
 
dplsrAuthor Commented:
nope acctualy string is correct then you can convert it. I have the same thing on another one that does work returning @@identity after an insert


  Dim addreturninfo As ASPNET.StarterKit.Commerce.ReturnOrdersDB = New ASPNET.StarterKit.Commerce.ReturnOrdersDB()
   Dim returnID as string = addreturninfo.AddReturnInfoToCMRCRGA(strOrderID, strEmail, strfirstname, strlastname)  

strReturnID = cint(ReturnID)
0
 
JaffaKREECommented:
Do you need the parameter ?

Dim lastreturnID as string = getlastId.GetLastReturnIDFromCMRCRGA (myIdent)
0
 
dplsrAuthor Commented:

nope, cause the parameter is an output . i will award u the points and start a new question  under  asp.net. got to be the way i am calling it.

thanks!
0
 
JaffaKREECommented:
Thanks, good luck finishing it off !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.