Post reply

insert stored procedure not working

  • January 28, 2005 at 10:49 am

    #61231

    I've been trying to troubleshoot a SP for 3 days now and still cannot resolve this.

    I need to insert records into two tables with a connecting table, lets say tableA, tableB, with connecting tableAB.

    My code checks to see if there is a record in tableA:

    SELECT * from tableA WHERE tableA.PK= @KeyValue

    IF @@RowCount <> 0

      RETURN

    END IF 

    INSERT TableA

    --appropriate values, this works fine

    SET @locTableAKey=(SELECT SCOPE_IDENTITY())

    INSERT TableB

    --appropriate values, also working

    SET @locTableBKey=(SELECT SCOPE_IDENTITY())

    INSERT TableAB

       (

         TableAKey

        ,TableBKey

       )

    VALUES

    (

         @locTableAKey

        ,@locTableBKey

       )

    GO

    I simply cannot get a record into tableAB. Is there a way to step through a SP to see what gives?

    Thanks

    Sam

  • January 28, 2005 at 11:00 am

    #539591

    never mind. I finally found it. It was a typo. (That only took 3 days to find!!!)

    Sam

  • January 28, 2005 at 4:53 pm

    #539637

    If you have SQL2K and have it running under a local user account rather than as Local System you should be able to step into your stored procedure. To do this open the object browser in Query Analyzer and drill down to the procedure in question. Then right click on it and hit "Debug". This should pop up a dialog box for you to fill in the parameters to pass to the SP. When you hit execute it will step into the code and stop at the first executable line. You can then use the F10, F11, F5, and F9 keys to set break points, step in, step over, or run the sp. This can be very handy!

    If you have Visual Studio you can also do this with a Database Project and use the standard Visual Studio keystrokes for debugging.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 28, 2005 at 5:01 pm

    #539638

    hey thanks, Gary. I had been looking for the debug feature in QA and couldn't find it. Ever feel like you're ice skating and your skates keep heading out from under you? I feel like I'm grasping  the wall trying to make my first circle.

    Sam

  • January 28, 2005 at 5:50 pm

    #539643

    I remember the feeling. Even if it was a LONG time ago. I actually feel that way right now with C# to some extent. I know just enough to make myself dangerous!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply