Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 5th, 2009, 08:54 AM
Authorized User
Join Date: Dec 2007
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default function returning identity value


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.

-- =============================================
CREATE FUNCTION dbo.getkey(@dbname varchar) 
RETURNS numeric
-- Declare the return variable here
DECLARE @Result numeric;
-- Add the T-SQL statements to compute the return value here
insert seqaccmst DEFAULT VALUES;
select @result=ident_current((@dbname );
-- Return the result of the function
RETURN @Result;
a thing like this will work fine in oracle by i just cant find out whats wrong in this. Please help

Last edited by dash dev; August 5th, 2009 at 08:57 AM..
Old January 21st, 2010, 05:01 PM
Registered User
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default maybe this will help, maybe not

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function returning an array rfstuck Pro VB 6 5 March 12th, 2008 07:35 AM
Function not returning value civa Access 5 January 17th, 2006 03:44 AM
Multi returning function reguengos Pro VB 6 2 March 3rd, 2005 01:48 PM
Function not returning anything... goatboy Beginning PHP 2 December 1st, 2003 12:34 PM
Returning variables from a function starsol Beginning PHP 1 September 11th, 2003 12:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.