Views Collection - Microsoft SQL / MS SQL Server

[SDF]

I want to create a SP that is passed a View Name - but I
can't use this variable as an object name within a Select
statement. My question: Can I create a View object by
querying the View Collection and use that object within
the Select Statement?

[Jens]

Hello ...... (Always post your real Name in this newsgroup !)

Try using dynamic SQL
---
Create Procedure Test Viewname varchar(50)
AS
DECLARE SQLSTatement Nvarchar(50)

Set SQLStatement = 'Select * from ' + Viewname

Exec SP_ExecuteSQL SQLStatement
---


HTH, Jens Süßmeyer.

[Scott]

Jens,

Thanks for the tip - but my problem is defining a Select
statemnt for CURSOR. It seems I can not use a variable
for the Select for a Cursor.

Something like this.

DECLARE SQLSTatement Nvarchar(50)
Set SQLStatement = 'Select DISTINCT(SiteParam) as SP
from ' + Viewname

declare c1 cursor for SQLStatement

viewName was passed to the SP and is valid. But it
doesn't accept the variable.

Any Ideas?

Scott
 
newsgroup !) 

[Vishal]

use dynamic sql
Ex:

exec ('declare c1 cursor for ' + SQLStatement)

--
-Vishal
"Scott Fredrick" <com> wrote in message
news:085001c368ca$5c5d3280$gbl...
Jens,

Thanks for the tip - but my problem is defining a Select
statemnt for CURSOR. It seems I can not use a variable
for the Select for a Cursor.

Something like this.

DECLARE SQLSTatement Nvarchar(50)
Set SQLStatement = 'Select DISTINCT(SiteParam) as SP
from ' + Viewname

declare c1 cursor for SQLStatement

viewName was passed to the SP and is valid. But it
doesn't accept the variable.

Any Ideas?

Scott
 
newsgroup !) 

[Joe]

The short answer is use slow, proprietrary dynamic SQL to kludge a query
together on the fly with your table name in the FROM clause.

The right answer is never pass a table name as a parameter. You need to
understand the basic idea of a data model and what a table means in
implementing a data model.

1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.

2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember the
basics of Software Engineering? Modules need weak coupling and strong
cohesion, etc.

3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw.

Go back to basics. What is a table? A model of a set of entities or
relationships. EACH TABLE SHOULD BE A DIFFERENT KIND OF ENTITY.

What a generic procedure that works equally on automobiles, octopi or
Britney Spear's discology is saying that your applications a disaster of
design because you have:

1) failed to put all items of the same kind into one table. Chris date
calls this orthogonal design, and I call it attribute splitting. Common
example, 12 identical tables, one for each month, with the same
information them instead of a single table with a temporal attribute.

2) failed to tell the difference between data and meta-data. The SQL
engine has routines for that stuff and applications do not work at that
level, if you want to have any data integrity.

Yes, you can write a program with dynamic SQL to kludge something like
this. it will last about a year in production and then your data
integrity is shot.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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