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 ?

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.

pootle flump ur codings are working excelent.

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.

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

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.

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.

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?

pootle flump ur codings are working excelent.

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...

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.

pootle flump ur codings are working excelent.

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.

Yep, I was kinda of hinting at not using the parameter. There is something to be said about undocumented code. Eh?