Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Unanswered: Transaction w/in Stored Procedure woes...

    I can't seem to get this to run...any help will be appreciated
    Code:
    CREATE PROCEDURE dbo.enrollAdultMember
    
    --required variables for input
            @lname varchar(15),
            @fname varchar(15),
            @middle varchar(2),  -- null is ok
            @street varchar(15),
            @city varchar(15),
            @state varchar(2),
            @zip varchar(11),
            @phone varchar(14) = NULL,  -- null is ok
            @bday datetime,
            @pic image = NULL
    
            -- return codes
            -- 0 == success
            -- 1 == null value found
            -- 2 == system error
            -- 3 == length of string too long
    AS
    
    -- check for null values in required params
    IF @lname IS NULL
    Print 'Last name must have a non-null value'
    Return(1)
    
    IF @fname IS NULL
    Print 'First name must be a non-null value'
    Return(1)
    
    IF @street IS NULL
    Print 'Street must be a non-null value'
    Return(1)
    
    IF @city IS NULL
    Print 'City must be  non-null value'
    Return(1)
    
    IF @state IS NULL
    Print 'State must be a non-null value'
    Return(1)
    
    IF @bday IS NULL
    Print 'Birthday must be a non-null value'
    Return(1)
    
    IF LEN(@bday) > 13
    Print 'Value for birthday is too long. 13 characters total is the max'
    Return(3)
    
    IF(LEN(@state) != 2 )
    Print 'State must be 2 characters in length'
    Return(3)
    
    BEGIN TRAN
                     
                         INSERT  INTO member (lastname, firstname, middleinitial, photograph)
                         VALUES( @lname, @fname, @middle, @pic )                       
                           
    
                            INSERT INTO adult ( member_no, street, city, state, zip, phone_no, expr_date )
                            VALUES( IDENT_CURRENT('member'), @street, @city, @state, @zip, @phone, DATEADD(Year, 1, @bday) )
                           
    
                    IF @@rowcount = 0 OR @@ERROR != 0             
                    BEGIN
                            ROLLBACK TRAN
                            Print 'Insert failed....rolling back'
                            RETURN
                    END
    COMMIT TRAN
                           
    
    GO

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Haven't looked at the rest but you need

    BEGIN TRAN

    INSERT INTO member (lastname, firstname, middleinitial, photograph)
    VALUES( @lname, @fname, @middle, @pic )
    IF @@rowcount = 0 OR @@ERROR != 0
    BEGIN
    ROLLBACK TRAN
    Print 'Insert failed....rolling back'
    RETURN
    END


    INSERT INTO adult ( member_no, street, city, state, zip, phone_no, expr_date )
    VALUES( IDENT_CURRENT('member'), @street, @city, @state, @zip, @phone, DATEADD(Year, 1, @bday) )


    IF @@rowcount = 0 OR @@ERROR != 0
    BEGIN
    ROLLBACK TRAN
    Print 'Insert failed....rolling back'
    RETURN
    END
    COMMIT TRAN

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    And consider raiserror rather than a print statements.

  4. #4
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Thanks Nigel,

    I also wrapped my validation within a begin end which pointed me in the right direction,

    gracias!!

    PAS

Posting Permissions

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