Create View in stored procedure

  • Hello,

    I'm new here and currently learn SQL Server. For my program I try to create a view from a stored procedure. I learned in other forums that views can't be created from a stored procedure directly. Following work around is necessary:

    Create Procedure [ProcedureName]

    As

    EXEC ('Create view As Select...')

    I can create view by using my "create view" statement when I use an editor. The same statement doesn't work, when I use it as a part of my stored procedure.

    In general it looks like I can't execute any statements in the form EXEC ('DROP VIEW...') or EXEC ('SELECT * FROM...') from the stored procedure.

    Does anybody have an idea for the reason and how can the problem be solved?

    Thank you.

  • You can't create a view from a stored procedure.

    Why do you need to create a view from a proc? A view is something that should be created and left on the database, then gets accessed. There shouldn't be a need for dynamic views. That's called a query. What is it that you're trying to do with creating views within procs?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • newcomer wrote:

    Hello,

    Does anybody have an idea for the reason and how can the problem be solved?

    Grant is correct about wanting to create a view in a sp but to answer your question:

    https://www.sommarskog.se/grantperm.html

    • This reply was modified 3 months, 4 weeks ago by  Ken McKelvey.
    • This reply was modified 3 months, 4 weeks ago by  Ken McKelvey.
  • Thank you for your answer. I need a data collection from different tables, that a can request via an interface from my application. I suppose it could be a good way. So, I planned avoiding multiple table requests and longer waiting time.

  • In real life, I've created views from stored procedures with no problems at all using dynamic SQL.  There were two reasons to do so... "Pass through" views to support "Swap'n'Drop" tables loads long before synonyms were made available and the monthly update of Partitioned Views, which work very well when correctly planned.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • As Jeff noted, you should be able to do this with dynamic SQL.

    But be aware that you cannot have any GO statements in the code.  Just the main statement itself:

    CREATE VIEW <viewname> AS ...

    If you're still having trouble, please post the actual code you are trying to run dynamically.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Here is a code example

    CREATE OR ALTER PROCEDURE [art].[pCreateArtList_View]

    AS

    EXEC ('CREATE VIEW [art].[vArtList]

    AS

    SELECT art.ArtList.*, loc.location.City, loc.location.Branch

    FROM art.ArtList JOIN loc.location ON art.ArtList.artNo = loc.location.artNo')

    ---------------

    EXEC ('CREATE VIEW [art].[vArtList]

    AS

    SELECT art.ArtList.*, loc.location.City, loc.location.Branch

    FROM art.ArtList JOIN loc.location ON art.ArtList.artNo = loc.location.artNo')

    creates view, when I start it from the editor. When I put

    CREATE OR ALTER PROCEDURE [art].[pCreateArtList_View]

    AS

    upfront EXEC the view is not created.

  • Keep in mind that the view is being created in the same db as the proc is in.  Although presumably that is what you want to do.

    What does "doesn't work" mean?  What error did you get?

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • That is exactly I wonder. I don't get any error messages. Instead of that I get "Commands completed successfully." but nothing happens.

  • P.S. Refresh doesn't help too.

  • And the view did not exist before the proc was called and still did not exist after it was called?

    If so, put the SQL in a variable and print it out before EXEC'ing it, just to be sure:

    DECLARE @sql nvarchar(max)

    SET @sql = 'CREATE VIEW [art].[vArtList]

    AS

    SELECT art.ArtList.*, loc.location.City, loc.location.Branch

    FROM art.ArtList JOIN loc.location ON art.ArtList.artNo = loc.location.artNo'

    PRINT @sql

    EXEC(@sql)

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Ummmmm... I don't see anywhere where the OP did an EXEC [art].[pCreateArtList_View]... 😉

    You DO have to actually execute the stored procedure for IT to create the view.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks. This seems to be a problem. When I EXEC [art].[pCreateArtList_View] in the editor, I get a message, that the EXECUTE is denied due to my permissions.

  • When you execute dynamic SQL in a stored procedure you need to have the permissions to execute the SQL inside the dynamic SQL.

    This is not true for non-dynamic SQL as the permissions are given to the stored procedure when the stored procedure is compiled so if you have the permission to execute the the store procedure you also have the permission to execute the non-dynamic contents of the stored procedure.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply