Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Simple Stored Proc question

    I have a stored procedure that looks like this-

    IF NOT EXISTS
    (Expression evaluates to true)
    BEGIN
    INSERT INTO Table
    (Account, CustomerName )
    VALUES
    (@Account, @CustomerName)

    SELECT Column FROM Table
    WHERE PrimaryKey = PrimaryKeyJustCreatedAbove
    END

    I'm creating a new record in the table, after I create this record I want to select the columns from the newly created record. Problem is the only unique value is the primarykey that is created with the record. Is there a way to identify that value within the statement?
    Thanks,
    Bill

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I feel like Carnac(sp?)

    The Answer is: SCOPE_IDENTITY()

    Follow the instructions in this link

    http://weblogs.sqlteam.com/brettk/ar...5/25/5276.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, bill, you forgot to mention which column is the primary key

    p.s. it's Karnak, the Magificent

    bonus points: on what television show would you find Karnak the Magnificent, and in what kind of container and on whose front porch were the questions stored all day, and how was that container sealed?
    Last edited by r937; 09-21-05 at 14:58.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Posts
    3
    What about

    SELECT * FROM Table where Account=@Account and CustomerName =@CustomerName

    I don't understand very well what are you trying to do with this, tell me more and i'll try

    Quote Originally Posted by beeyule
    I have a stored procedure that looks like this-

    IF NOT EXISTS
    (Expression evaluates to true)
    BEGIN
    INSERT INTO Table
    (Account, CustomerName )
    VALUES
    (@Account, @CustomerName)

    SELECT Column FROM Table
    WHERE PrimaryKey = PrimaryKeyJustCreatedAbove
    END

    I'm creating a new record in the table, after I create this record I want to select the columns from the newly created record. Problem is the only unique value is the primarykey that is created with the record. Is there a way to identify that value within the statement?

  5. #5
    Join Date
    Mar 2004
    Posts
    139
    Thanks for the responses...my apologies for the incomplete explanation but I think Carnac got it with SCOPE_IDENTITY.

    Basically I am creating a new record with an INSERT statement and two column values, neither of these columns are primary keys. When the record is created a primarykey value will be created with an identity column. If I understand it correctly SCOPE_IDENTITY will return the last IDENTITY value inserted in that column and I can use that value in the WHERE clause of the SELECT statement.
    Thanks,
    Bill

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup, it was Carnac

    http://en.wikipedia.org/wiki/Johnny_Carson

    Again though, what's with the existance check?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And here I thought you guys were the same

    http://www.dbforums.com/t1191236.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Posts
    139
    The existence check has to do with a value that is passed in from the application. If it exists it opens up that record, if not it creates a new one. I didn't need to include it in this post...2 black eyes...
    Thanks,
    Bill

Posting Permissions

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