T-SQL how to deal with results from stored proc

[Wolfgang Kreuzer]

Try hard to become familiar with T-SQL.

Can anybodey tell me the best way to deal with set's provided by a
stored procedure. Til yesterday I thougt trapping set in temp table
using INSERT EXEC is a way out of this, but then I struggeled with
nested INSERT EXEC's.

What are all the system proc's good for if the results cannot be
evaluated? The approach of modular programming is to have code doing
similar things in one place.

If I try to make use of sp_helprolememb er to get login names for more
roles, pack the logins in one table and return the result set in a SP,
the procedure which calls that is unable to evaluate the set.
On the other hand I read the advice, not to access system tables
directly.

Is there a way out?

[John Bell]

Hi

If the system procedures do not provide what you want directly then you can
look at the code to see how they gather the information and then write your
own. I have personally rarely found that I have needed to embed and evaluate
the results from system stored procedures, but if you think that this should
be a mandatory requirement for these procedure then you may want to put the
suggestion forward to sq*****@microso ft.com.

You may want to look at your design and see if you are trying to re-invent
functionality that is available elsewhere, or if you are pitching the
application at the wrong level.

John

"Wolfgang Kreuzer" <wo************ ****@gmx.de> wrote in message
news:1120984025 .eedd1f00afef5f d7e145c7bdbfbea 0c3@teranews...

Try hard to become familiar with T-SQL.

Can anybodey tell me the best way to deal with set's provided by a
stored procedure. Til yesterday I thougt trapping set in temp table
using INSERT EXEC is a way out of this, but then I struggeled with
nested INSERT EXEC's.

What are all the system proc's good for if the results cannot be
evaluated? The approach of modular programming is to have code doing
similar things in one place.

If I try to make use of sp_helprolememb er to get login names for more
roles, pack the logins in one table and return the result set in a SP,
the procedure which calls that is unable to evaluate the set.
On the other hand I read the advice, not to access system tables
directly.

Is there a way out?

[Erland Sommarskog]

John Bell (jb************ @hotmail.com) writes:

If the system procedures do not provide what you want directly then you
can look at the code to see how they gather the information and then
write your own. I have personally rarely found that I have needed to
embed and evaluate the results from system stored procedures, but if you
think that this should be a mandatory requirement for these procedure
then you may want to put the suggestion forward to
sq*****@microso ft.com.

I would say that Microsoft has already started to take this direction.
In SQL 2005, plenty of well-establish system stored procedure are
becoming deprecated. Instead the approach is that you should get
system data from the new catalog views. of which sum may actually be
table-valued functions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Wolfgang Kreuzer (wo************ ****@gmx.de) writes:

Try hard to become familiar with T-SQL.

Can anybodey tell me the best way to deal with set's provided by a
stored procedure. Til yesterday I thougt trapping set in temp table
using INSERT EXEC is a way out of this, but then I struggeled with
nested INSERT EXEC's.
Look at http://www.sommarskog.se/share_data.html for a couple of
alternatives.
What are all the system proc's good for if the results cannot be
evaluated? The approach of modular programming is to have code doing
similar things in one place.
The concepts of SQL does not always fit entirely with the ideas of
modular programming.

In modular programming you compute some pieces here and some pieces
there. But in SQL the idea is that you give the entire thing to be
computed to the query optimizer and the optimizer will estimate what is
the most effecient order make this computation. For system metadata
this is not likely to be important, but if the query involves tables with
several million rows, this is getting essential.

That said, SQL does provide you modularisation through views and
inline table-functions. But the problem with a view is that while
it gives you the result that you need, it may perform computations
that you don't need for your current problem at hand.

For a complex system, modularisation is still required, because
duplicating logic is never good. But it should not be taken too far.
And partciularly, one needs to understand where modularisation may be
too expensive for performance.
On the other hand I read the advice, not to access system tables
directly.

The only problem with the system tables is that in SQL 2005 they are
deprecated in favour of the catalog views. But in SQL 2000 there is not
that much alternatives.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Wolfgang Kreuzer]

Erland,
thanks for your quick and exhaustive answer.

And thanks for not trying to convince me that modular programming is
the wrong approach. I understand that having a good data model which
allows to query/calculate the results is the main thing in SQL but
sometimes not the solution.

Wolfgang

[David Portas]

Could you give us an example of when "having a good data model" is NOT
appropriate?

Maybe if you explained the scenario with DDL and sample data we could make
some suggestions more specific to your actual problem.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

Could you give us an example of when "having a good data model" is NOT
appropriate?

The standard example would be when you have a third-party application,
and you are supposed to write some home-brewed queries for reports. In
this situation you don't start redesigning the data model, how flawed it
may be.

Although, my guess is that Wolfgang tried to say even if you have a good
data model, that alone does evade the needs for modularizing your code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Stu]

Amen. This is the first post I've read in a long time that recognizes
that the ideal solution may be beyond the control of the developer. I
know I deal with a lot of poorly designed databases that have to share
data, and I'm sure ohers do as well.

Stu

[Wolfgang Kreuzer]

On Sun, 10 Jul 2005 16:17:07 +0100, "David Portas"
<RE************ *************** *@acm.org> wrote:

Could you give us an example of when "having a good data model" is NOT
appropriate? No - what I wanted to express in short (maybe it was a bit to short)
is
- I agree with Erland that in 99 % of the cases query data a leave it
with the optimizer to find a way to retrieve the data required is
certainly the right approach
- avoiding redundancies (normalised structure - to a certain degree
(somewhere between 2.5th and 3rd normal form) - is a key issue

I don't want dig more into detail on that 'data model' thing ...


Maybe if you explained the scenario with DDL and sample data we could make
some suggestions more specific to your actual problem. My problem I was trying to solve is, checking membership in several
role at the beginning of stored procedures.

My attempt was to use INSERT EXEC to fetch te results of several
sp_helprolememb er calls in a temp table (in some stored proc;
up_EnumSuperUse rs; up_EnumApplAdmi ns ...) and return collected data as
a record set. In a wrapping stored proc I intended to collect data
from some of the proc's above and check if current user or specified
user is in the list - but as far as I know evaluating data from a
stored proc record set requires INSERT EXEC which does not allow
nesting for whatever reason.

On the other hand, UDF's do not allow usage of non-deterministic
functions, call of stored proc, INSER, CREATE etc.

Having that in mind and following Kalen Delaney advise - never use
direct access to system tables, use system stored proc instead, you
end up in the middle of nowhere, ring-fenced by DONT's without Gates.

What I have done is, I took the SQL statement out of sp_helprolememb er
put it in several UDF's which just returns a BIT value indicating if
someone is member of a role or some of them. Having in mind (now -
hopefully later, when new version of SQL server is rolled out, as
well) that I may have to rewrite code (I normally hate things like
that- and try to avoid it whereever possible).

--
David Portas
SQL Server MVP

[Erland Sommarskog]

Wolfgang Kreuzer (wo************ ****@gmx.de) writes:

My attempt was to use INSERT EXEC to fetch te results of several
sp_helprolememb er calls in a temp table (in some stored proc;
up_EnumSuperUse rs; up_EnumApplAdmi ns ...) and return collected data as
a record set. In a wrapping stored proc I intended to collect data
from some of the proc's above and check if current user or specified
user is in the list - but as far as I know evaluating data from a
stored proc record set requires INSERT EXEC which does not allow
nesting for whatever reason.

On the other hand, UDF's do not allow usage of non-deterministic
functions, call of stored proc, INSER, CREATE etc.
You can also use temp tables, as I discuss in the article I pointed you to.
What I have done is, I took the SQL statement out of sp_helprolememb er
put it in several UDF's which just returns a BIT value indicating if
someone is member of a role or some of them.

I don't know exactly what you are doing, but have you looked at the
built-in function is_member()?

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp