Create View in stored procedure

Question

Create View in stored procedure

newcomer

SSC Enthusiast

Points: 177
More actions
January 27, 2022 at 9:41 am

#3977834

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.
December 7, 2022 at 6:27 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ken McKelvey SSCoach Points: 18766 More actions · Answer 2

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 2 years, 2 months ago by Ken McKelvey.
This reply was modified 2 years, 2 months ago by Ken McKelvey.

newcomer SSC Enthusiast Points: 177 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

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)

ScottPletcher SSC Guru Points: 100946 More actions · Answer 5

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) A socialist is someone who will give you the shirt off *someone else's* back.

newcomer SSC Enthusiast Points: 177 More actions · Answer 6

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.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 7

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) A socialist is someone who will give you the shirt off *someone else's* back.

newcomer SSC Enthusiast Points: 177 More actions · Answer 8

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

newcomer SSC Enthusiast Points: 177 More actions · Answer 9

newcomer

SSC Enthusiast

Points: 177

January 27, 2022 at 4:14 pm

#3977973

P.S. Refresh doesn't help too.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 10

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 11

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)

newcomer SSC Enthusiast Points: 177 More actions · Answer 12

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.

Jonathan AC Roberts SSCoach Points: 18258 More actions · Answer 13

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.