retrieve auto number ID
-
Hi, I am inserting a new record to a SQL 2005 database table(tClients). I have a Primary Key called iClientID in that I use to bind clients, that is Auto Number. I want to retrieve the iClientID once I inserted the record, as part of my stored procedure. Here is my insert statement.
INSERT INTO tClients(sClientName, sClientAddress, sClientContact, iClientOrder) VALUES ('Joe Simpson', '3 Green Ave' , 'Mr. R Simpson' , 3) -
Hi, I am inserting a new record to a SQL 2005 database table(tClients). I have a Primary Key called iClientID in that I use to bind clients, that is Auto Number. I want to retrieve the iClientID once I inserted the record, as part of my stored procedure. Here is my insert statement.
INSERT INTO tClients(sClientName, sClientAddress, sClientContact, iClientOrder) VALUES ('Joe Simpson', '3 Green Ave' , 'Mr. R Simpson' , 3)DECLARE @Id INT
INSERT INTO tClients(sClientName, sClientAddress, sClientContact, iClientOrder)
VALUES ('Joe Simpson', '3 Green Ave' , 'Mr. R Simpson' , 3)
SELECT @Id = SCOPE_IDENTITY();
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
DECLARE @Id INT
INSERT INTO tClients(sClientName, sClientAddress, sClientContact, iClientOrder)
VALUES ('Joe Simpson', '3 Green Ave' , 'Mr. R Simpson' , 3)
SELECT @Id = SCOPE_IDENTITY();
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
playout wrote:
I just have one more question... What is SCOPE_IDENTITY()?
What you asked for. The ID of the most recently inserted row (in this scope).
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Regards, Satips.