|
|
Posted by Wolfgang Kreuzer on 07/10/05 22:09
On Sun, 10 Jul 2005 16:17:07 +0100, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@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_helprolemember calls in a temp table (in some stored proc;
up_EnumSuperUsers; up_EnumApplAdmins ...) 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_helprolemember
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
Navigation:
[Reply to this message]
|