SP to add next sequential user ID

[jimatqsi]

I'm just starting to write stored procedures. It took me hours to get this simple thing to work. Now I'm wondering what is the right way to do this. All I want to do is add a new row to the Users table and return the newly created UserID.

USE MessyTextSQL
GO
/****** Object:  StoredProcedure [dbo].[AddNewUser]    Script Date: 11/06/2011 07:40:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].AddNewUser
-- =============================================
-- Author:        Jim Wolf
-- Create date: 11/7/11
-- Description:    Add new MessyText project
-- =============================================
  -- Add the parameters for the stored procedure here
  @NewUserID int output
AS
BEGIN
  SET NOCOUNT ON;
 
  -- Insert statements for procedure here
  INSERT INTO Users(UserID)
  VALUES (99999999)
  SET @NewUserID =SCOPE_IDENTITY()
  Update Users
  Set UserID = cnt from Users where Users.cnt =@NewUserID
 
END
 

Thanks for any advice,

Jim

[ck9663]

Here, read this...

Happy Coding!!!

~~ CK

[jimatqsi]

Thanks, but that doesn't tell me much except that I could use @@IDENTITY or IDENT_CURRENT instead of SCOPE_IDENTITY.

Is there no way to set the UserID column to = the new identity without doing it in two separate steps of INSERT and UPDATE?

Thanks,
Jim

[Rabbit]

I don't understand. Why are you storing the cnt field twice?

[jimatqsi]

Because I believe it's a really bad idea to use an auto-increment field as a primary key. Is that still true?

Jim

[Rabbit]

I've heard the opposite. For all intents and purposes, using a surrogate key as a primary key has more advantages than disadvantages. And many of those disadvantages can be mitigated by careful design and consideration.

You can read more about surrogate keys here.

But more importantly, when someone says that using a surrogate key is a bad idea, they don't mean that you should copy the key into another field to use as a primary key. They mean that you should drop the surrogate key altogether and use a natural key.

[Maraj]

Hy jimatqsi!Why are you making it so complex if you want to return user id you only need a select statement after you have inserted new row.

[jimatqsi]

Thanks very much, Rabbit. The idea to copy the auto-generated ID to another column for the key is simply to guard against a problem if the table ever has to be rebuilt, which could generate new ID numbers and cause PK-FK problems.

My newly generated ID is, in fact, a natural key. New users get a new ID and ID numbers are generated sequentially. It's not like an order or invoice table that could have a key that matches a document ID.

So, in summary, it looks like using auto-generated ID numbers for keys is no longer frowned upon. I'll change my habit.

Thanks,

Jim

[Rabbit]

If you ever have to rebuild a table with an identity (autonumber), you can still insert into that table with the identity defined and it won't create a new number. Also, you could drop the identity and put it back on later if you wanted.

[ck9663]

Just remember, unless the identity column is the primary key, it cannot insure uniqueness. Whether it is the primary key or not, it cannot insure sequence either.

Happy Coding!!!


~~ CK