Professional Web Applications Themes

Returning the newly create PK - Microsoft SQL / MS SQL Server

I've been talked into implementing my dB calls in ADO.net using stored procedures to do things I'd normally do with adhoc SQL in-line code. Therefore, I have the following code to INSERT a record into a table. The PK for the table is an identity, so it will automatically assign a new IS for the new record. I need my stored procedure to get that newly assigned number and return it to me so I can use it right after the save. Please advise.... Not sure if I'm declaring the output value the right way or maybe even how to ...

  1. #1

    Default Returning the newly create PK

    I've been talked into implementing my dB calls in ADO.net
    using stored procedures to do things I'd normally do with
    adhoc SQL in-line code. Therefore, I have the following
    code to INSERT a record into a table. The PK for the
    table is an identity, so it will automatically assign a
    new IS for the new record.

    I need my stored procedure to get that newly assigned
    number and return it to me so I can use it right after
    the save.

    Please advise.... Not sure if I'm declaring the output
    value the right way or maybe even how to use it in the
    code calling... I'll call anyone anywhere on the phone to
    discuss this if it easier..

    ---------------------------------------
    CREATE PROCEDURE spInsertCourse
    CourseID varchar(20) OUTPUT, Title varchar(255),
    CourseNumber varchar(20),
    Description text, CategoryID numeric, Duration tinyint,
    MinAttend tinyint, MaxAttend tinyint, Audience varchar
    (255),ProviderID tinyint, Sources varchar(255),
    Prerequisites varchar(255),
    HIDTAOwner numeric, EntryPerson numeric, EntryDate
    smalldatetime
    AS
    INSERT INTO tblCourse
    (Title,CourseNumber,Description],CategoryID,Duration,MinAt
    tend,MaxAttend,Audience,ProviderID,Sources,Prerequ isites,H
    IDTAOwner,EntryPerson,EntryDate)
    VALUES
    (Title,CourseNumber,Description,CategoryID,Du ration,
    MinAttend,MaxAttend,Audience,ProviderID,Source s,Prere
    quisites,HIDTAOwner,EntryPerson,EntryDate)
    GO

    Mark Guest

  2. #2

    Default Re: Returning the newly create PK

    disclaimer: I'm below novice level.

    I use IDENTITY or SCOPE_IDENTITY() as described here.
    http://www.aspfaq.com/2174

    Ray at work


    "Mark in miami" <org> wrote in message
    news:0fb001c35cea$098fd670$gbl...
     


    Ray Guest

  3. #3

    Default Re: Returning the newly create PK

    I presume CourseID is PK. You can get CourseID value from identity variable as shown below:

    CREATE PROCEDURE spInsertCourse
    (
    CourseID varchar(20) = null OUTPUT
    , Title varchar(255)
    , CourseNumber varchar(20)
    , Description text
    , CategoryID numeric
    , Duration tinyint
    , MinAttend tinyint
    , MaxAttend tinyint
    , Audience varchar (255)
    , ProviderID tinyint
    , Sources varchar(255)
    , Prerequisites varchar(255)
    , HIDTAOwner numeric
    , EntryPerson numeric
    , EntryDate smalldatetime
    )

    AS

    set nocount on

    INSERT INTO tblCourse
    (Title
    ,CourseNumber
    ,Description
    ,CategoryID
    ,Duration
    ,MinAttend
    ,MaxAttend
    ,Audience
    ,ProviderID
    ,Sources
    ,Prerequisites
    ,HIDTAOwner
    ,EntryPerson
    ,EntryDate)
    VALUES
    (Title
    ,CourseNumber
    ,Description
    ,CategoryID
    ,Duration
    ,MinAttend
    ,MaxAttend
    ,Audience
    ,ProviderID
    ,Sources
    ,Prerequisites
    ,HIDTAOwner
    ,EntryPerson
    ,EntryDate)

    set CourseID = identity


    --
    Dean Savovic
    www.teched.hr


    "Mark in miami" <org> wrote in message news:0fb001c35cea$098fd670$gbl... 


    Dean Guest

Similar Threads

  1. newly registered cfx tag problem
    By pelican in forum Coldfusion Server Administration
    Replies: 6
    Last Post: February 20th, 01:31 PM
  2. Newly Installed Illustrator CS Won't Run
    By ATL@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 3
    Last Post: April 22nd, 02:32 PM
  3. Cannot login to newly create user in XP
    By Help in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 27th, 12:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139