How to Modify a views' SQL source from a program

[Paul Scotchford]

Hi all, Thanks in advance to any contributions to my question.

Im running SQLServer 2000 in a Win 2000 Server env.

Background:
==========

The database has many views that range from simple to complex joins on
tables.

The selection criteria is fixed eg. 'Where TaskTypeIdent = 2204 and
OutcomeId = 123 or 2322 or 1222 and
CicType = 87878 ... etc etc

Requirement:
===========

Now what I would like to do is be able to change the 'where =' value
part ie. 2204, dynamically if and when required, and it will be
required.

In other words there won't be a DBA handy to do this when it changes
hence I would like to write a front end UI to allow the user to easily
manage this.

There are upwards of 200 views like this.

Question:
========

Where in the system metadata can I access (if possible) the source of
the View such that I can update it with the new values. eg. 'Where
TaskTypeIdent = 7627 and OutcomeId = 2322 or 94847 or 989 and CicType =
1111 ... etc etc

Many thanks

Paul
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Paul Scotchford <pa*****************@eds.com> wrote in message news:<40*********************@news.frii.net>...

Hi all, Thanks in advance to any contributions to my question.

Im running SQLServer 2000 in a Win 2000 Server env.

Background:
==========

The database has many views that range from simple to complex joins on
tables.

The selection criteria is fixed eg. 'Where TaskTypeIdent = 2204 and
OutcomeId = 123 or 2322 or 1222 and
CicType = 87878 ... etc etc

Requirement:
===========

Now what I would like to do is be able to change the 'where =' value
part ie. 2204, dynamically if and when required, and it will be
required.

In other words there won't be a DBA handy to do this when it changes
hence I would like to write a front end UI to allow the user to easily
manage this.

There are upwards of 200 views like this.

Question:
========

Where in the system metadata can I access (if possible) the source of
the View such that I can update it with the new values. eg. 'Where
TaskTypeIdent = 7627 and OutcomeId = 2322 or 94847 or 989 and CicType =
1111 ... etc etc

Many thanks

Paul
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You can use ALTER VIEW to change a view's definition, but you should
probably consider a different approach. In general, you don't want
users creating or modifying any objects in your database, because
allowing them to do so would create significant security and
maintenance issues.

It's not clear from your description why you hard-code values at all -
why not simply pass the values to your query at runtime, perhaps as
stored procedure parameters? And/or replace views with table-valued
functions, which accept parameters for the search values?

If your fundamental requirement is for reporting, you may want to look
at using a reporting package which will build queries for your users
directly (MS Reporting Services, Crystal Reports etc.). Or for a
stored procedure solution, check out this article:

http://www.sommarskog.se/dyn-search.html

Simon

[--CELKO--]

>> Now what I would like to do is be able to change the 'where ='
value part ie. 2204, dynamically if and when required, and it will be
required. <<

That is called a PROCEDURE and not a VIEW.

In other words there won't be a DBA handy to do this when it

changes hence I would like to write a front end UI to allow the user
to easily manage this. <<

Giving users the power to write code on the fly to users is like
giving a teenage boy car keys and whiskey. Messing with schema
information tables is like doing your own dental work with garden
tools. What are you thinking, man?

Possible answer: You can build a table of constants within a schema
using Standard SQL like this and reference them inside the VIEWs:

CREATE TABLE Constant
(lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY
CHECK (lock = 'X'),
pi REAL DEFAULT 3.141592653 NOT NULL,
e REAL DEFAULT 2.718281828 NOT NULL,
phi REAL DEFAULT 1.618033988 NOT NULL,
..);

INSERT INTO Constants DEFAULT VALUES;

The insertion creates one row, so the table ought to have a singular
name. The "lock" column assures you that there is always only one
row. Now you can let users update this table to change things, and
have a way to re-set it to default values. But put a LOT of DCL on
this table!!

[Paul Scotchford]

Thanx but you lost the plot somewhere. Obviously I did not explain
myself simply enough. But obviously I will have to refer to books
instead. CELCO you jugemental approach in your reply is indicative of an
arrogance know it all. Why waste time typing a reply when all you give
is a misguided opinion rather than some technical facts, man you are
useless...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[--CELKO--]

>> Thanx but you lost the plot somewhere. Obviously I did not explain
myself simply enough. But obviously I will have to refer to books
instead. CELKO you jugemental approach in your reply is indicative of
an arrogance know it all. Why waste time typing a reply when all you
give is a misguided opinion rather than some technical facts, man you
are useless... <<

What is your problem? Simon told you the same thing about letting
users write code on the fly; he was not as witty about it (did you
recognize the P.J. O'Rouke quote?). I also did give you a possible
solution that would not endanger the applications or the database. I
gave you a simple, portable way that you can "re-set" the schema to
default values, as a safety.

I feel that this is a much better approach than using an ALTER
statement and then trying to remember what a VIEW looked like 42
revisions ago (assuming the end users have the skills to write correct
code). What happens in that scenario is that User A changes the VIEW
and since he does not need column x, he drops it. When user B
executes the VIEW, he does need column x and it is gone. Or worse, a
calculation is changed but the column name is not.

If you want to do reports of some kinds, get a report writer. Much
easier, much cheaper in the long run, and much safer.

Horrible true story. I did a consulting job many years ago at a drug
company. The sales support database was on INFORMIX and all the
programmers had full DBA access to it. The database got doctor
information (name, address, specialities, etc.) from outside sources.
One programmer liked to use (I think I have the names right) Med-Data
and the other liked Phoenix.

The codes these services use for specialties are totally different --
numeric versus alpha, three versus five, etc.

The programmers each wrote ALTER TABLE, ALTER VIEW scripts to change
the database on the fly to use their favorite data source. The schema
was not the same for more than a day or two. Try to find that
problem! The solution was to pick one source and stick with it, and to
assign a single DBA.

You are trying to repeat the same scenario, but change
(DBA/programmers) to (programer/users).

[Paul Scotchford]

Well I should have realised that often these forums are little more than
a platform for so called "know it alls" to show off their limited
knowledge, but I have found some threads that have been written by
experts.

I found my solution and have coded it thankyou and it serves the purpose
well for the environment I have to develop a solution for.

Your assertions reflect your lack of understanding of development
processes.

Thankyou for your input.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!