Assign new ID value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Assign new ID value

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2011-05-16 : 07:35:13

I need this assignNewID as a function. to be called for each row.
( sql 2008, I don't have the sequence object yet )

CREATE PROC Stocks.AssignNewUniqueID( @NewID BIGINT OUTPUT )
AS

DECLARE @TranCounter INT
DECLARE @TranCounter2 INT
DECLARE @TransactionName VARCHAR(50) = 'TranNameAssignID'
SET @TranCounter = @@TRANCOUNT

IF @TranCounter > 0
SAVE TRANSACTION @TransactionName
ELSE
BEGIN TRAN @TransactionName
INSERT INTO Stocks.AssignUniqueIDs DEFAULT VALUES
ROLLBACK TRAN @TransactionName
SET @NewID = SCOPE_IDENTITY()
GO

Noam Graizer

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-16 : 07:48:01

What is the question? It isn't clear from your code what you are trying to accomplish.

Jim

Everyday I learn something that somebody else already knew

noamg
Posting Yak Master

215 Posts

Posted - 2011-05-16 : 07:56:21

I need a function and not store procedure

Noam Graizer

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-16 : 08:06:19

And what do you need this Function to do? Your sproc code says "take @newID as an input and then set it to the scope_identity". The scope_identity is a function already, so I'm still not clear what you are trying to accomplish.

Jim

Everyday I learn something that somebody else already knew

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-16 : 08:06:51

What's wrong with using an identity column on the table itself?

There are many ways to get the self-generated ID wrong and very few ways to get it right.

--
Gail Shaw
SQL Server MVP

noamg
Posting Yak Master

215 Posts

Posted - 2011-05-16 : 08:32:55

it is not for identity column.
I need to generate a unique id value, and update diff columns.
in batch, so each column get new value

update #suggestion
set winCode = Games.MyNewID(),
takeCode = Games.MyNewID()
where ...

hope now it is clear

Noam Graizer

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-16 : 09:11:19

What's wrong with Rob's suggestion?

UPDATE @Suggestions SET Priority=RAND(CAST(CAST(NEWID() AS binary(4)) AS int))

Jim

Everyday I learn something that somebody else already knew

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources