[dba-SQLServer] Difference between views and queries

Arthur,

Your opinion is not the issue, the way in which you present it is.
Whether you are personally ignorant or not, your posts on this topic
have failed to display much evidence of being aware of the subtleties
of the issue although you have not shied from making bold sweeping
statements without suitable caveats. Reviewing some of your posts,

"Dynamic SQL is NOT the way to go once you port to SQL Server. You
need to rethink all these parts of your app and replace them with
sprocs that can handle all the parms you might pass to your
SQL-construction code."

"NO users except sa (and possibly developers) should have access to
any SQL table. Everything should be done with views or sprocs or
UDFs. No exceptions."

"I haven't yet seen a case where dynamic SQL is necessary. All it
takes to avoid it is one or more well-constructed sprocs, IMO."

Given your most recent comment to Billy,

"Good example, and good reasoning, and I agree that dynamic SQL is
never out of the question."

should I assume that you were never ignorant of an example of a good
use for dynamic SQL but just decided to post your claims as if there
were no reasonable uses anyway or should I assume as you state in
your last post that you were ignorant. Note, I do not care in whether
reading the posts from David Lewis or myself on how Billy's problem
could be done without using dynamic sql changes your opinion back
to no dynamic sql usage again. I just challenge you to question
whether you really think you have sufficiently researched this topic
to make the kind of claims you originally made. At the least, for
those who read your statements to gain knowledge of the subject, if
you truly are aware of the issues as you state, you might consider
mentioning them in your post. BTW, feel free to requote yourself
in more detail if you think you are taken out of context, but I
think you will fail to find any qualifications you added to your
statements.

Your example for different problems with different SQL dialects is
actually rather interesting. You present the problems with insisting
on no dynamic sql quite well but don't state this is another valid
example where dynamic sql may be the only choice. As you have proved
the case for usage, I am certainly not going to argue with you.

I have no real way of knowing whether you understand dynamic search
from your example, but I will be fair and state my references, so you
can be sure I do. Working with Sommarskog's dynamic sql summary,
http://www.sommarskog.se/dyn-search.html#dynsql, I move to section
"Using Static SQL" and try to find your example. You seem to be
referring to "Using IF statements" which is cute, but I immediately
class myself out of this problem as I do not have just a few primary
search parameters. Wrapping the alternatives is, as he says an
acceptable variation, but it doesn't change the fact that I can't
use this approach.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Arthur Fuller
> Sent: Wednesday, June 30, 2004 11:09 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Difference between views and queries
> 
> 
> You have a backhanded way of calling me ignorant. In many ways and in
> many areas I am ignorant, but just because I hold an opinion that
> differs from yours, that's no reason for you to assume that I 
> am unaware
> of the issues you raise. I worked on a project for ABB which 
> relied on a
> database comprising over 400 tables and ran in both Oracle and SQL
> Server. Both versions used sprocs of the same name. Where there were
> differences in syntax, they were encapsulated in the sprocs. From the
> viewpoint of the various Front End modules, it didn't matter which
> database you were talking to. The FE had virtually no knowledge of the
> back end flavour, other than the connection. Now, granted, should ABB
> decide suddenly to support MySQL, and given MySQL's limited support of
> sprocs, well then there is a problem. Any fool can recognize 
> that -- but
> substitute Access for MySQL and you have the same problem. This is
> beneath discussion.
> 
> Regarding dynamic search, I have done at least one form that 
> presents 25
> controls and invokes one sproc that in turn invokes others, 
> depending on
> which parameters are null. No problem there. Add a control later in
> development, I change a sproc and maybe add another few. No problem
> there either.
> 
> Does this constitute research into the issues involved?
> 
> Arthur
<snip>