• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

Get the key of recently inserted record!

I use SQL Server for store database.
And use Trigger to generate the primary key for new records.

I have problem when I write a programme on Winform:
   When I insert a record into database, I wanna know what the key SQL gennerate for it is so that I can use it for the future.

Show is any good way to do that?
0
tunhien
Asked:
tunhien
  • 5
  • 3
1 Solution
 
AGBrownCommented:
The SQL function SCOPE_IDENTITY will return the identity used for the last record inserted. You can use a stored procedure to do your insert, and return the value of SCOPE_IDENTITY as an OUTPUT parameter from the stored procedure.

If you need help on specifics, just say.

Andy
0
 
tunhienAuthor Commented:
If I use store procedure then no need to use trigger! and SCOPE_IDENTITY because I can return the key I generated. :d

Show is there any way to do that with C# code not SQL Sever?
Thanks
0
 
AGBrownCommented:
I don't think I mentioned triggers. Also, if you use a stored procedure, the most efficient way to get the identity you have just inserted _is_ to use SCOPE_IDENTITY. Otherwise, how would you do it? You could do a SELECT Id From... using your inserted values, but that is very messy.

So the only way to do it with C#, if you don't want to do it with a stored procedure and SCOPE_IDENTITY is to execute a second statement after your insert which does just that ... selects the id based on a WHERE clause that finds your inserted values.

So how would you do it with a stored procedure? I'm intrigued.

Andy
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tunhienAuthor Commented:
It's sound good way!

But please show me more about the store procedure! How can U generate code automatically?

If I've got a table like this:

create table test
(
         code       int,     -- Need to auto increase
         primary key (code)
)


Please write the SP to do that when Insert and return is the key has just been inserted!
Thanks

0
 
AGBrownCommented:
Heres a stored procedure that will do what you want, and the create table script to go with it...

CREATE TABLE [dbo].[test] (
      [code] [int] IDENTITY (1, 1) NOT NULL ,
      [primaryKey] [int] NOT NULL ,
GO

CREATE PROC DoMyInsert
(
    @PrimaryKey varchar(50),
    @AnotherColumn varchar(50),
    @intIdCreated int OUTPUT
)
AS
INSERT INTO dbo.test
    (primaryKey, ...)
VALUES
    (@PrimaryKey, @AnotherColumn)

SELECT @intIdCreated = SCOPE_IDENTITY()
GO
0
 
AGBrownCommented:
PS this way you can get rid of the trigger as well? Or do you need that for another reason?
0
 
tunhienAuthor Commented:
uupppp!

Thanks for your suggestion! I now I knew the IDENTITY in SQL server. :"> :d
I will try with the new way instead of trigger. :d
0
 
AGBrownCommented:
good luck!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now