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
9 2115
Here, read this...
Happy Coding!!!
~~ CK
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
I don't understand. Why are you storing the cnt field twice?
Because I believe it's a really bad idea to use an auto-increment field as a primary key. Is that still true?
Jim
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.
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.
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: cliff williams |
last post by:
How can I get a form to open with the next sequential number as set by me
|
by: cliff williams |
last post by:
how can I set a form so that when i open it , it appears with the next
sequential number. I want to set the number pattern and not use
autonumber
|
by: Earl Anderson |
last post by:
In A97 on WinXP, I'm trying to use a KB function to provide the next
sequential number in a custom counter (a table with two fields--
and ). When I run it, I get the "User-defined
type not...
|
by: Bijoy Naick |
last post by:
I am inserting controls dynamically into my page; both Web Controls and User
Controls. Code Below..
I noticed that inspite of specifying the ID for the user controls, in the
HTML, the name is...
|
by: timasmith |
last post by:
Hi,
I have this app which is relatively database independent. I would like
to serve the smallest scale (MS Access) up to the largest database
server (Oracle).
I recoginize that I will need...
|
by: Mythran |
last post by:
I have:
Class Library -
User Control with Build Action set to "Embedded Resource" in a class
library.
User Control code-behind file Build Action set to "Compile".
Web Application -
Web page...
|
by: Mythran |
last post by:
I apologize for the need to x-post this thread. The original thread is
getting pretty old and no replies in
microsoft.public.dotnet.framework.aspnet. Also, I'm aware that this isn't a
C# only...
|
by: azoapes |
last post by:
Hi, I've made a Setup Project to my C# WinApp and added a custom dialog to it that asks for user/pass for a database server. Then I created a Custom Action class and override the Install() to check...
|
by: Axxe |
last post by:
I have searched high and low for cogent, well-explained coding to complete a project on which I have spent six months of work.
I stumbled across something on this site that is close to what I...
|
by: ghjk |
last post by:
In my php page I have many results. I want to display only 20 records per page and there should be link like 1,2,3,Next >>. When user click 2 it will goes to the second page. How can I do that?
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |