How to write this stored procedure

Question

I have a table Student. This table has a column ID which is auto-increment. I want to write a stored procedure to add a new student and return the ID. I'm newbie at this.

This is my code. Please check if it's wrong and fix for me and show me how to code to use it in C#. I used Entity Framework 4.

@Name NVARCHAR(50),
@Birthday DATETIME, 
@ID bigint OUTPUT 
AS 
BEGIN
    SET XACT_ABORT ON 

    BEGIN 
       INSERT INTO [Student](Name, Birthday) VALUES (@Name, @Birthday);  
       SELECT @ID = SCOPE_IDENTITY()
    END
END

Yes, you need to show us that you're asking for help, and not asking us to do your work for you. — John Saunders, Nov 06 '12 at 03:27

score 3 · Accepted Answer · edited May 23 '17 at 10:24

3

It is better you can C# code instead SP when your working with EF4.

Using Entity Framework, this is all done automagically for you.

using (MyEntities context = new MyEntities())
{
    var student =  new Student()
    {
      Name = "some value",
      Birthday = "some Birthday"
    };

    context.Students.AddObject(student);

    context.SaveChanges();

    int ID = student.ID; // You will get here the Auto-Incremented table ID value.
}

Saving in Entity Framework will automatically update the "auto-increment" ID column. You just read it out after the call to .SaveChanges();

EDIT:

Also read this post if you encounter any issues getting the "auto-increment" ID value.

edited May 23 '17 at 10:24

Community

1
1

answered Nov 06 '12 at 03:26

Prasad Kanaparthi

6,423
4
35
62

Question: why only small-sized records? – John Saunders Nov 06 '12 at 03:28
Sorry it is Typo.. You can use for any :). Edited post accordingly. Thanks John for finding. – Prasad Kanaparthi Nov 06 '12 at 03:30

score 0 · Answer 2 · answered Nov 06 '12 at 03:09

0

@Name NVARCHAR(50),
@Birthday DATETIME, 
@ID bigint OUTPUT
AS
BEGIN
SET XACT_ABORT ON   
BEGIN 

    INSERT INTO [Student](Name,Birthday)
    VALUES(@Name,@Birthday);

    SELECT @ID = SCOPE_IDENTITY()
END

I just added commas in between fields

answered Nov 06 '12 at 03:09

Patrick Guimalan

990
9
11

score -1 · Answer 3 · answered Nov 06 '12 at 03:02

-1

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

This should provide you all the information you need to build a C# application calling your Stored Procedure.

answered Nov 06 '12 at 03:02

user1801742

21
3

So true, but it was used to create a SP which can be used correctly with the SQLCommand object. Although, I understand that the answer provided by Prasad Kanaparthi is a better alternative. – user1801742 Nov 06 '12 at 12:00

How to write this stored procedure

3 Answers3