Avatar of rhservan
rhservan
Flag for United States of America asked on

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?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Member_2_276102

8/22/2022 - Mon
Anthony Perkins

Please post your Stored Procedure and we can help you further.
rhservan

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?
Anthony Perkins

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rhservan

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?
Anthony Perkins

are you saying there is no way to tell unless you look at each different stored procedure?
That is precisely what I am stating.
rhservan

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Member_2_276102

You shouldn't be calling stored procs if you don't know what they do.

Tom
rhservan

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?
Anthony Perkins

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Member_2_276102

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:
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
Anthony Perkins

Tom,

Very good write-up and will prove useful to all future readers.

Thank you,
Anthony
Member_2_276102

@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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.