How can I tell where a new column starts in a stored procedure?
Newbie
In executing a stored procedure the result set returns six columns. But when I look at the stored procedure I am unable to decipher the queries used to create each of the columns. Is there a common way to determine the query to column output in any stored procedure?
In executing a stored procedure the result set returns six columns. But when I look at the stored procedure I am unable to decipher the queries used to create each of the columns. Is there a common way to determine the query to column output in any stored procedure?
Anthony Perkins
Please post your Stored Procedure and we can help you further.
ASKER
I am speaking of stored procedures in general not specific to any one stored proc.
In executing a stored procedure the result set returns six columns. But when I look at the stored procedure I am unable to decipher the queries used to create each of the columns. Is there a common way to determine the query to column output in any stored procedure?
In executing a stored procedure the result set returns six columns. But when I look at the stored procedure I am unable to decipher the queries used to create each of the columns. Is there a common way to determine the query to column output in any stored procedure?
I guess I am not following you. A quick look at any Stored Procedure will tell you immediately if there are 0, 1 or more result sets returned and the columns in each query or queries.
ASKER
how do you know if there are 0,1 or more result sets by looking at it. What is the determining factor?
OR
are you saying there is no way to tell unless you look at each different stored procedure?
OR
are you saying there is no way to tell unless you look at each different stored procedure?
are you saying there is no way to tell unless you look at each different stored procedure?
That is precisely what I am stating.
That is precisely what I am stating.
ASKER
A quick look at any Stored Procedure will tell you immediately if there are 0, 1 or more result sets returned and the columns in each query or queries.
Please explain then what I would look for that would immediately tell me if there are 0,1 or more results set to query in the stored procedure.
Please explain then what I would look for that would immediately tell me if there are 0,1 or more results set to query in the stored procedure.
You shouldn't be calling stored procs if you don't know what they do.
Tom
Tom
ASKER
Thanks Tom that is a great response coming from a high level expert. I am so far beneath you. I guess I will just give up now and stop trying to learn stored procedures. Such wisdom.
Please do not clutter up my comment boxes without providing solutions!!
Anyone else want to give it a shot?
Please do not clutter up my comment boxes without providing solutions!!
Anyone else want to give it a shot?
Please explain then what I would look for that would immediately tell me if there are 0,1 or more results set to query in the stored procedure.
If there are no SELECT statements that return results then 0
If there is one SELECT statement that return results then 1
If there are more than one SELECT statements that return results then 2 or more
If there are no SELECT statements that return results then 0
If there is one SELECT statement that return results then 1
If there are more than one SELECT statements that return results then 2 or more
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The question was self-identified as "Newbie", so a full description isn't reasonably possible. There are ways to do it, but it requires much more experience and knowledge. If there wasn't a way, then even Microsoft clients couldn't do it.
For a "Newbie", a stored proc might cause any number of potentially damaging effects. It might kick off an end-of-period roll-up process or generate a massive cross-product join query that brings the server to its knees if done under inappropriate circumstances.
With basic experience, you know what the stored proc does because you:
As for determining if there are 0, 1 or more result sets, you look for SELECT statements that aren't sub-SELECTs, and you count them. Further, you account for any conditional statements that might cause 0, 1 or more of those SELECT statements to run under different conditions.
Part of the problem is that SQL Server stored procs are not necessarily deterministic. With one set of parameter values, the stored proc might return 0 result sets. With other values, the same stored proc might return three result sets. Or it might always return exactly one result set but with different numbers of columns and data types. Even with the same parameter values, outputs may change due to other values from the database.
So, for 'generic stored procs', the only real answer is to read the code and figure it out. With some reasonably advanced .NET coding or other procedural code, it's possible to build utilities that do some of the analysis for you. It's very hard to be certain, though, without essentially running the stored proc code at least in your head.
Tom
For a "Newbie", a stored proc might cause any number of potentially damaging effects. It might kick off an end-of-period roll-up process or generate a massive cross-product join query that brings the server to its knees if done under inappropriate circumstances.
With basic experience, you know what the stored proc does because you:
Read the stored proc's documentation.
Read the stored procs source and run it in your head to determine what it does.
Ask the developer.
Know how you defined things if you defined it yourself.
Run with SET FMTONLY ON and hope it works as you want. (There are problems with relying on it because conditional logic is ignored.) The stored proc might even implement code that forces FMTONLY OFF, thereby defeating your request.
What you're asking seems to be "How do I read source of a stored proc?" And that seems to include how to read a SELECT statement in the source of a stored proc. If you've looked at the stored proc and can't decipher the SELECT (or SELECTs), then the best answer is to study the SELECT statement until you understand them. That means going to SQL Server DBMS documentation and learning it.As for determining if there are 0, 1 or more result sets, you look for SELECT statements that aren't sub-SELECTs, and you count them. Further, you account for any conditional statements that might cause 0, 1 or more of those SELECT statements to run under different conditions.
Part of the problem is that SQL Server stored procs are not necessarily deterministic. With one set of parameter values, the stored proc might return 0 result sets. With other values, the same stored proc might return three result sets. Or it might always return exactly one result set but with different numbers of columns and data types. Even with the same parameter values, outputs may change due to other values from the database.
So, for 'generic stored procs', the only real answer is to read the code and figure it out. With some reasonably advanced .NET coding or other procedural code, it's possible to build utilities that do some of the analysis for you. It's very hard to be certain, though, without essentially running the stored proc code at least in your head.
Tom
Tom,
Very good write-up and will prove useful to all future readers.
Thank you,
Anthony
Very good write-up and will prove useful to all future readers.
Thank you,
Anthony
@Anthony:
Thank you. My initial response was abrupt, and I don't mind offering an apology to rhservan. That response wasn't intended to be an "answer", but only a definite principle. I left the answer to others for reasons that can be clear with experience. Your first responses were really all that was needed; but it's hard to figure out how to give true, complete answers that have tough technical foundations when you don't know the experience level of the audience (the OP in particular). In this case, a really good answer that actually worked and was efficient could be a potentially valuable commercial product in itself. That's not easy to communicate, though.
Tom
Thank you. My initial response was abrupt, and I don't mind offering an apology to rhservan. That response wasn't intended to be an "answer", but only a definite principle. I left the answer to others for reasons that can be clear with experience. Your first responses were really all that was needed; but it's hard to figure out how to give true, complete answers that have tough technical foundations when you don't know the experience level of the audience (the OP in particular). In this case, a really good answer that actually worked and was efficient could be a potentially valuable commercial product in itself. That's not easy to communicate, though.
Tom