function returning identity value

dash dev · August 5th, 2009, 08:54 AM

hi!

i just migrated from oracle to sql server.
My problem is that we use compound primary keys which consists of an integer and a branch id. Our db requires merging operation from time to time. So we can not keep the identity column as part of the pr.key. thats why we kept it in a separate table which is only used for generating the identity value. this table does not contain any data it just emulates a sequence like in oracle.

to get the last identity value of any given table i created this function which does not work and gives compile errors.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE FUNCTION dbo.getkey(@dbname varchar) 
RETURNS numeric
AS
BEGIN
-- Declare the return variable here
DECLARE @Result numeric;
-- Add the T-SQL statements to compute the return value here
 
insert seqaccmst DEFAULT VALUES;
rollback;
select @result=ident_current((@dbname );
-- Return the result of the function
RETURN @Result;
END
GO

a thing like this will work fine in oracle by i just cant find out whats wrong in this. Please help

Denmach · January 21st, 2010, 05:01 PM

Have you looked at books online for @@IDENTITY or IDENT_CURRENT?

I'm not sure exactly what you are trying to do here, maybe it's because I'm exhausted from lack of sleep. That said, these functions might help you out, or they might not be what you're looking for and I could be way off base. Either way it's accurate information about a few things TSQL has thats slightly different than PLSQL.

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

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.

If none of this helps you out, after some sleep I'll pick up this thread again tomorrow or the day after. I don't know if you have to maintain identity in a seperate table with Transact SQL's abilities or not.

I have just 5 years of TSQL DBA xp and nearing 2 of Oracle. Good luck mate, your mileage may vary.