Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Creating View by calling Stored Proc

    Hello all,
    Is it possible to create a view by running the Stored Procedure in SQL Server 2000?

    Thanks in advance,
    Venugopal

  2. #2
    Join Date
    Sep 2003
    Posts
    8
    Create Procedure CreateView
    as

    declare @sql varchar(8000)

    set @sql =
    'Create View MyView
    as
    select * from MyTable'

    drop view MyView

    exec(@sql)

    grant select on MyView to public

    go

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Thanks for the reply. But what i was looking for what
    Create View MyView As Exec SP1

    Now i am able to do the same with OPenRowset, but the problem with that, it does allow the view to be schema bound without that i can't provide index to it.


    Originally posted by mdhealy
    Create Procedure CreateView
    as

    declare @sql varchar(8000)

    set @sql =
    'Create View MyView
    as
    select * from MyTable'

    drop view MyView

    exec(@sql)

    grant select on MyView to public

    go

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Can't you just call the SP from the client?

    Openrowset will create a new connection and can cause unexpected problems.

  5. #5
    Join Date
    Nov 2003
    Posts
    5
    Originally posted by nigelrivett
    Can't you just call the SP from the client?

    Openrowset will create a new connection and can cause unexpected problems.


    No because requirement is that i have to write the query in access.
    Currently there is a link table from Access to SQL Server. So if i create a view then there is no need for duplication and storing of data. Thats the reason i want to create the view with a SP.

    Thanks

Posting Permissions

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