Professional Web Applications Themes

Views Collection - Microsoft SQL / MS SQL Server

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?...

  1. #1

    Default Views Collection

    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?
    SDF Guest

  2. #2

    Default Re: Views Collection

    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.


    Jens Guest

  3. #3

    Default Re: Views Collection

    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 !) 
    Scott Guest

  4. #4

    Default Re: Views Collection

    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 !) 


    Vishal Guest

  5. #5

    Default Re: Views Collection

    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!
    Joe Guest

Similar Threads

  1. Persisting collection data of a webcontrol when leaving the collection editor in VS2005
    By mehdi.mousavi@gmail.com in forum ASP.NET Building Controls
    Replies: 1
    Last Post: May 19th, 03:49 PM
  2. Replies: 0
    Last Post: July 22nd, 09:31 PM
  3. How to bing a collection with sub-collection to grid
    By gzinger in forum ASP.NET Data Grid Control
    Replies: 11
    Last Post: July 5th, 02:28 AM
  4. Replies: 2
    Last Post: May 24th, 08:07 PM
  5. Replies: 4
    Last Post: July 8th, 07:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139