Professional Web Applications Themes

create views from parameterized stored procedures - Microsoft SQL / MS SQL Server

I'm looking to do as the topic says, I currently use temporary real tables (not #temp tables) that i keep in existence for just long enough for an access adp frontend to issue an output to excel command, and then it's dropped. The parameters are usually 2 dates to form a range or a few char fields. The cry for me to use views instead has been issued, and i'm investigating changing my select into queries into create view statements. My first problem is: "Server: Msg 111, Level 15, State 1, Line 4 'CREATE VIEW' must be the first statement ...

  1. #1

    Default create views from parameterized stored procedures


    I'm looking to do as the topic says, I currently
    use temporary real tables (not #temp tables) that
    i keep in existence for just long enough for
    an access adp frontend to issue an output to excel
    command, and then it's dropped. The parameters are
    usually 2 dates to form a range or a few char fields.
    The cry for me to use views instead has been issued,
    and i'm investigating changing my select into queries
    into create view statements.
    My first problem is:
    "Server: Msg 111, Level 15, State 1, Line 4
    'CREATE VIEW' must be the first statement in a query
    batch."
    when i try a simple create view statement after
    defining a few variables. If i'm to issue go statements
    to get that working, i'll lose the definition of my
    parameters.
    So what are my options then, to use global variables?
    Further, does anyone know if the performance impact is
    worthwhile? The main reason for the change to views is
    to avoid the confusion of these tables with the permenant
    ones, but i'm curious about performance.

    thx ahead of time

    -g
    gregg durishan Guest

  2. #2

    Default Re: create views from parameterized stored procedures

    gregg,

    although it's possible to create a view within a stored procedure, it's not
    recommended to maintain these objects. you would definitely have prob with
    concurrency. if you post your procedure code, someone might give a better
    solution.

    at any rate, you could use dynamic query to create the view within a sproc.

    e.g.
    create proc _usp
    as
    exec('create view _v as select * from northwind..orders')
    go


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "gregg durishan" <evilmousseNOSPAM.hotmail.com> wrote in message
    news:094801c34505$ea058850$a301280aphx.gbl...
    >
    > I'm looking to do as the topic says, I currently
    > use temporary real tables (not #temp tables) that
    > i keep in existence for just long enough for
    > an access adp frontend to issue an output to excel
    > command, and then it's dropped. The parameters are
    > usually 2 dates to form a range or a few char fields.
    > The cry for me to use views instead has been issued,
    > and i'm investigating changing my select into queries
    > into create view statements.
    > My first problem is:
    > "Server: Msg 111, Level 15, State 1, Line 4
    > 'CREATE VIEW' must be the first statement in a query
    > batch."
    > when i try a simple create view statement after
    > defining a few variables. If i'm to issue go statements
    > to get that working, i'll lose the definition of my
    > parameters.
    > So what are my options then, to use global variables?
    > Further, does anyone know if the performance impact is
    > worthwhile? The main reason for the change to views is
    > to avoid the confusion of these tables with the permenant
    > ones, but i'm curious about performance.
    >
    > thx ahead of time
    >
    > -g

    oj Guest

  3. #3

    Default Re: create views from parameterized stored procedures

    >although it's possible to create a view within a stored
    procedure, it's not
    >recommended to maintain these objects. you would
    definitely have prob with
    >concurrency. if you post your procedure code, someone
    might give a better
    >solution.
    no, i'm pretty sure there's a very low chance of
    concurrency problems. Very rarely will any two people
    run the same output to excel report within the
    2-6 seconds it takes from table/view creation to
    excelOutput to dropping.
    Either way the concurrency issue remains tables OR
    views.
    >at any rate, you could use dynamic query to create the
    view within a sproc.
    >
    >e.g.
    >create proc _usp
    >as
    >exec('create view _v as select * from northwind..orders')
    >go
    hmmm, so how does that get me my parameters?


    here's a POC sample i've been toying with.
    declare from datetime, to datetime
    select from = '1/1/1', to = '2/2/2'
    create view vw1 as
    select * from t1
    where dt1 between from and to

    Thx for the speedy reply tho
    -g

    gregg durishan Guest

  4. #4

    Default Re: create views from parameterized stored procedures

    declare sql varchar(1000)
    set sql=' create view vw1 as
    select * from t1
    where dt1 between '+quotename(from,char(39))+' and
    '+quotename(to,char(39))
    exec(sql)


    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]



    "gregg durishan" <evilmousseNOSPAM.hotmail.com> wrote in message
    news:820501c3450c$0fa806e0$a401280aphx.gbl...
    >
    > >although it's possible to create a view within a stored
    > procedure, it's not
    > >recommended to maintain these objects. you would
    > definitely have prob with
    > >concurrency. if you post your procedure code, someone
    > might give a better
    > >solution.
    > no, i'm pretty sure there's a very low chance of
    > concurrency problems. Very rarely will any two people
    > run the same output to excel report within the
    > 2-6 seconds it takes from table/view creation to
    > excelOutput to dropping.
    > Either way the concurrency issue remains tables OR
    > views.
    >
    > >at any rate, you could use dynamic query to create the
    > view within a sproc.
    > >
    > >e.g.
    > >create proc _usp
    > >as
    > >exec('create view _v as select * from northwind..orders')
    > >go
    >
    > hmmm, so how does that get me my parameters?
    >
    >
    > here's a POC sample i've been toying with.
    > declare from datetime, to datetime
    > select from = '1/1/1', to = '2/2/2'
    > create view vw1 as
    > select * from t1
    > where dt1 between from and to
    >
    > Thx for the speedy reply tho
    > -g
    >

    oj Guest

  5. #5

    Default Re: create views from parameterized stored procedures

    simply...

    declare from datetime, to datetime, sql varchar(1000)
    select from = '1/1/1', to = '2/2/2'
    --select convert(datetime, from, 101)
    set sql = 'create view vw1 as select * from t1 where dt1 =
    '+quotename(convert(varchar,from,101),char(39))
    print sql
    exec(sql)

    --
    -oj
    RAC v2.2 & QALite!
    [url]http://www.rac4sql.net[/url]


    "gregg durishan" <evilmousseNOSPAM.hotmail.com> wrote in message
    news:0f0001c34565$08d4c0c0$a501280aphx.gbl...
    > >declare sql varchar(1000)
    > >set sql=' create view vw1 as
    > > select * from t1
    > > where dt1 between '+quotename(from,char(39))+' and
    > >'+quotename(to,char(39))
    > >exec(sql)
    >
    > tricky! i like it!
    >
    > however, now the problem is the format in which
    > quotename shows the date. i can tell from a
    > debug output that it's in an english form rather
    > than a numeric one. I tried converting the date
    > param prior to it's call, but still no good...
    > i'm very glad you helped me learn the quotename
    > function tho.
    >
    > declare from datetime, to datetime, sql varchar(1000)
    > select from = '1/1/1', to = '2/2/2'
    > select convert(datetime, from, 101)
    > set sql = 'create view vw1 as select * from t1 where dt1
    > = '+quotename(from,']')
    > print sql
    > exec(sql)

    oj Guest

Similar Threads

  1. Stored Procedure or Views?
    By cfdyn in forum Coldfusion Database Access
    Replies: 1
    Last Post: November 12th, 01:05 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Replies: 1
    Last Post: September 27th, 01:51 PM
  5. Parameterized Views
    By Nice Chap in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 4th, 01:12 PM

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