SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Assign new ID value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

noamg
Posting Yak Master

Israel
197 Posts
Posted - 05/16/2011 :  07:35:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 05/16/2011 :  07:48:01  Show Profile  Reply with Quote
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
Go to Top of Page

noamg
Posting Yak Master

Israel
197 Posts
Posted - 05/16/2011 :  07:56:21  Show Profile  Reply with Quote
I need a function and not store procedure

Noam Graizer
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 05/16/2011 :  08:06:19  Show Profile  Reply with Quote
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
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts
Posted - 05/16/2011 :  08:06:51  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

noamg
Posting Yak Master

Israel
197 Posts
Posted - 05/16/2011 :  08:32:55  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 05/16/2011 :  09:11:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000