Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Exclamation Unanswered: insert stored procedure with error check and transaction function

    Hi, guys
    I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
    could you correct my code, if you know what is the problem?

    thanks

    My contract table DDL:
    ************************************************** ***

    create table contract(
    contractNum int identity(1,1) primary key,
    contractDate smalldatetime not null,
    tuition money not null,
    studentId char(4) not null foreign key references student (studentId),
    contactId int not null foreign key references contact (contactId)
    );


    My insert stored procedure is:
    ************************************************** *****

    create proc sp_insert_new_contract
    ( @contractDate [smalldatetime],
    @tuition [money],
    @studentId [char](4),
    @contactId [int])
    as

    if not exists (select studentid
    from student
    where studentid = @studentId)
    begin
    print 'studentid is not a valid id'
    return -1
    end

    if not exists (select contactId
    from contact
    where contactId = @contactId)
    begin
    print 'contactid is not a valid id'
    return -1
    end
    begin transaction

    insert into contract
    ([contractDate],
    [tuition],
    [studentId],
    [contactId])
    values
    (@contractDate,
    @tuition,
    @studentId,
    @contactId)

    /*Error Check */
    if @@error !=0 or @@rowcount !=1
    begin
    rollback transaction
    print ‘Insert is failed’
    return -1
    end
    print ’New contract has been added’

    commit transaction
    return 0
    go

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

    exec sp_insert_new_contract
    @contractDate = '01/01/2004',
    @tuition = 3000,
    @studentId = 'ABCD',
    @contactId = 1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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