Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Complicated Stored Procedures!

    Ive just joined a new project that has an existing database that is very complicated. There are a LOT of tables with LOTS of fields and keys, and the table names arnt very helpful with figuring things out.

    The stored procedures are equally complicated. I'm looking at one now that is about 1000 lines long, takes 4 parameters, and performs TONS of joins and uses temporary tables and is really hard to figure out.

    But I shouldnt really NEED to figure out the stored procedure. I should be able to treat it as a 'black box', and only worry about the 4 parameters that go in, and whatever result set comes back.

    The problem is I'm trying to get some sample data back from the procedure, and Ive literally been spending hours trying to pass in some parameters that actually return a result. I get back an empty result set every time.

    So Ive been looking at the code in the procedure, trying to get some hints at what I should pass in for parameters. Ive determined the data types of the parameters, and looked at each table that the parameters are compared to, and really spent a lot of time trying to come up with some parameters that actually produce some output.

    Ive even been toying with the idea of writing my own stored procedure that takes another stored procedure as a parameter, and just throws different combinations of parameters at it until it returns a resultset. This would be a nightmare to write however, and hopefully theres an easier way.

    Has anyone ever been in my situation before? How do you figure out what are some good parameters to pass into a stored procedure without having to understand the procedure inside-and-out ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you speak to any users? Does anyone else know how to get a result from it?

    It might, of course, be that the procedure is simply b0rked and no combination will work. Ultimately, it sounds like you will need to take the code out and strip it down. Run the first query. Get a result? Move on to the next query. If not, figure out why. Basically break it down in to manageable chunks.

    In short, you can;t really troubleshoot something like this without getting your hands dirty. If it turns out the code is broken then yeah - you will have to get to know it inside out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Run a trace of the server. Unless the application is running in a Java engine, you should be able to see what parameters the application itself is passing.

  4. #4
    Join Date
    Feb 2010
    Posts
    75
    Im sure someone around here has a better idea what the SP is supposed to do than I. But before asking them, I just wanted to see if maybe there was a clever way to solve this without bothering anyone else. Afterall, I dont really WANT to know exactly what the SP does, I just want to cause it to return some data. I figured that was a much easier goal than actually understanding it. But from the sounds of it I guess I need to understand it anyway, in order to pass some useful params. Also, this is just 1 of probably 20 super complex Stored procedures, so I really would have rather had a simple solution other than just hunting down the SP author.

    MCrowley, if I understand you correctly, that wont help me. I am the one using the application. So wether I'm inputting the parameters into the front end application interface, or directly into the SQL stored procedure, I still dont know what to input. If I ran a trace, I think I would only get my own guesses at the parameters

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    here a trick I use open the Stored Procedures in SQL

    hightlight some of the SQL statment and you can EXE only the Highlighed stuff

    It should display and output in the bottom haft of the screen.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Find where the parameters are used, and then execute the query without passing in a parameter. Or use NULL value for value in parameter.

    select a.name,b.address_street,b.city
    from person a
    inner join address b on a.id = b.id
    where a.id = @id <-- ommit this and return a list of possible id values (top 10?)

    Repeat this process for each query that uses a passed in parameter until you reverse engineer the logic then you will have some values to use. Might take a few passes until you get all the correct parameter values but this is one possible solution.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are the only person that uses this application, and you don't know how to use it, what good actually is it?
    Is it really not used by anyone else?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2010
    Posts
    75
    myle, thanks, but I've known about that 'trick' for like 10 years
    Thats how Ive been deciphering the stored procedure so far, but its still so big that even breaking it down like that isnt helping much.

    corncrowe, leaving a parameter undefined or null causes the query to return nothing. In the example you provided, if @id was null, then no rows would be returned. I could get rid of the where clause entirely however...

    pootle flump, its basically a reporting application. We present a screen that gathers these parameters, presumably from a user who knows what to input, and produce the info for them to see. There is a live version of the application, which I dont have access to, and a development version, which I am working on, but dont have any way to grab example parameters (that I know of).

    Thanks for your help all. If no one else has any tricks for me, then I guess I have to bite the bullet and dissect this procedure line by line...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BeerOclock View Post
    pootle flump, its basically a reporting application. We present a screen that gathers these parameters, presumably from a user who knows what to input, and produce the info for them to see. There is a live version of the application, which I dont have access to, and a development version, which I am working on, but dont have any way to grab example parameters (that I know of).
    Can someone else run a trace on prod for you? Or can you speak to one of the users that uses the live application? Note that these are only worth doing if the development version contains the same data as prod.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Alternatively, you can try to get the knowledgeable user to use the test system you have, and you can trace what they do. If the data in the test system is out of date, it may take them a few tries to get a valid report, so you will want to have them on the phone as you do this.

  11. #11
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by BeerOclock View Post
    corncrowe, leaving a parameter undefined or null causes the query to return nothing. In the example you provided, if @id was null, then no rows would be returned. I could get rid of the where clause entirely however...

    Thanks for your help all. If no one else has any tricks for me, then I guess I have to bite the bullet and dissect this procedure line by line...
    Yep, I was kinda of hinting at not using the parameter. There is something to be said about undocumented code. Eh?

Posting Permissions

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