Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Creating A View in a Stored Procedure

    Hi Everyone

    Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

    CREATE PROC upProcName AS

    DECLARE @Variable varchar(50)

    CREATE VIEW vwName AS

    SELECT DISTINCT Table1.*, Table2.*
    FROM dbo.Table1
    INNER JOIN dbo.Table2 AS BUG
    ON Table1.Col1 = Table2.Col1
    WHERE LI.accname = @Variable

    GO

    Any Thoughts ideas would be great

    Cheers

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Creating A View in a Stored Procedure

    you can not create a view in a stored procedure.
    Create view should be the first statement of the batch.

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    I thought that may be the case. Is there a way to change the view or pass it parameters?

  4. #4
    Join Date
    Mar 2004
    Posts
    15
    I thought that may be the case. Is there a way to change the view or pass it parameters?

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Execute a sp_dboption YourDB, "DDL in tran", true

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    why would you want to create a view within a store procedure??

    I alway thought of views are used for
    1. quick query
    2. row level permission control

    in what other situations would a view be used for?? for the reasons above i don't see why you would need to dynamically create a view.


    cheers
    James

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this one?

    drop view test2
    drop proc test
    go
    create proc test
    as
    exec('create view test2 as select getdate() as now')
    select * from test2

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by snail
    What about this one?

    drop view test2
    drop proc test
    go
    create proc test
    as
    exec('create view test2 as select getdate() as now')
    select * from test2
    YUP!

    But I like the question of why you need to do this though?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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