Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: insert in a stored procedure

    say i have a table in my database which corresponds to a business object. in my application i create an instance of the business object class, say, CBusinessObject. Now I want to serialize this instance to the datastore. Is the best practice creating a stored procedure with an insert clause?

    also, if i do my INSERT INTO inside a stored procedure, how do i automatically use the value of the next largest primary key? as an example:

    Code:
    INSERT INTO dbo.MYTABLE 
    (
    prikey,
    col1,
    col2,
    col3,
    col4,
    )
    VALUES 
    (
    NewId(),
    @Param1,
    @Param2,
    @Param3,
    @Param4,
    )
    i get the following error if I use NewId()

    Operand type clash: uniqueidentifier is incompatible with int


    thank you in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If prikey is an identity you do not need to specify it in the insert as it will seed itself.
    Code:
    INSERT INTO dbo.MYTABLE 
    (
    col1,
    col2,
    col3,
    col4,
    )
    VALUES 
    (
    @Param1,
    @Param2,
    @Param3,
    @Param4,
    )
    Note that NewId() returns datatype uniqueidentifier.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    23
    if you are still working on this,
    the right way as georgev suggests is to use a IDENTITY field in your table, which will automatically insert the next higher value in the column.
    As far as the newid() goes, we can enter this value in a uniqueidentifier datatype column, for example:

    create table jk_temp1(f1 uniqueidentifier, f2 varchar(100))
    insert into jk_temp1 values(newid(),'testing newid')

    hope it helps
    jambu

Posting Permissions

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