SQL Server / Create view from stored procedure

Question

I am trying to create a view out of a stored procedure and am perplexed to see two opposing results from a very similar approach.

Example 1

CREATE PROCEDURE cv AS
GO
DECLARE @sql nvarchar(MAX)
SET @sql = 'CREATE VIEW test AS SELECT * FROM someOtherTable'
exec (@sql)

Whereas this example creates the view once the procedure is created for the 1st time, it will not recreate the view when I execute the procedure at a later stage using:

EXEC cv

Example 2

CREATE PROCEDURE cv 
@table SYSNAME
AS
DECLARE @sql nvarchar(MAX)
SET @sql = 'CREATE VIEW '+ @table +' AS SELECT * FROM someOtherTable'

This one instead does not create the view when the procedure is created for the first time but creates the view afterwards every time it is called by:

EXEC @sql;

Why is this the case? I think this is really confusing and does not make sense or does it?

CREATE PROCEDURE mean create procedure not view. When you execute SP then you call CREATE VIEW which is creates view. — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 28, 2014 at 11:01
It's wrong. Calling the same create view multiple times does not work. You must drop the view before creating it again. — jean
– jean, Commented Mar 28, 2014 at 11:04
CREATE PROCEDURE only creates the procedure not any Objects referring inside that procedure. Your observation about the 1st procedure is wrong, it cannot possibly create the view for you by just creating that procedure. — M.Ali
– M.Ali, Commented Mar 28, 2014 at 11:04
Also you are just creating the SP. Running that SP ill create a view. When you say creating the SP creates the view it's not correct — jean
– jean, Commented Mar 28, 2014 at 11:05

M.Ali · Accepted Answer · 2014-03-28 11:22:59Z

For your 1st statement

CREATE PROCEDURE cv AS
GO                      --<-- This GO here terminates the batch
DECLARE @sql nvarchar(MAX)
SET @sql = 'CREATE VIEW test AS SELECT * FROM someOtherTable'
exec (@sql)

the GO batch terminator create the procedure and the EXECUTES the following statement straightaway. So it appears to you as you have created a procedure which created the view for you.

Infact these are two statements in two batches.

--BATCH 1

CREATE PROCEDURE cv AS
GO

--BATCH 2

DECLARE @sql nvarchar(MAX)
SET @sql = 'CREATE VIEW test AS SELECT * FROM someOtherTable'
exec (@sql)

Batch 1 Creates a procedure which has nothing inside it, but it creates a procedure object for you with no functionality/Definition at all.

Statement after the key word GO is executed separately and creates the view for you.

My Suggestion

Always check for an object's existence before you create it. I would write the procedure something like this..

CREATE PROCEDURE cv 
AS
BEGIN
 SET NOCOUNT ON;

IF OBJECT_ID ('test', 'V') IS NOT NULL
 BEGIN
   DROP VIEW test
 END

    DECLARE @sql nvarchar(MAX)
    SET @sql = 'CREATE VIEW test AS SELECT * FROM someOtherTable'
    exec (@sql)
END

Julian Joseph · Accepted Answer · 2014-03-28 11:04:07Z

0

In Example 1 - you are creating a view with a hard-coded name of test. On subsequent runs of your proc, as the view already exists, SQL Server will throw an error because you are trying to create a view with the same name as one that already exists.

In Example 2, you are passing in the name of the view as a parameter. This will always create a new view unless the @table value you pass in corresponds to an existing view.

Just wondering - why are you creating a view with a stored proc? This is not something you would normally do in SQL.

answered Mar 28, 2014 at 11:04

Julian Joseph

1746 bronze badges

Comments

huMpty duMpty · Accepted Answer · 2014-03-28 11:12:26Z

0

You need to change

EXEC @sql to EXEC (sql)

EXEC can be use to run stored procedure, not dynamic sql

eg.

declare @dd varchar(100)='Select ''A'''

exec (@dd) ->will work fine
exec @dd   -> Error

Read more about The Curse and Blessings of Dynamic SQL

edited Mar 28, 2014 at 11:12

answered Mar 28, 2014 at 11:05

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

2 Comments

M.Ali Over a year ago

see my answer to this question, it does work :) the issue is with GO in the middle of procedure definition.

huMpty duMpty Over a year ago

@M.Ali : Sorry I didn't get you!! What is working? I just indicate the OP, how to execute dynamic sql

marc_s · Accepted Answer · 2016-11-26 20:51:15Z

0

Create view statement cannot be used with a stored procedure

edited Nov 26, 2016 at 20:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 28, 2014 at 11:44

Vignesh

2153 silver badges10 bronze badges

1 Comment

Arne Over a year ago

OK, i see on two occassions that a view should not be used inside a stored procedure. My rationale to create a procedure to create a view was to have an easy way to update (by recreating) the view by just calling it conveniently through the EXEC statement. Is there a better way to achieve a VIEW update?

Balamurugan · Accepted Answer · 2019-03-08 06:53:48Z

0

EXEC ('CREATE VIEW ViewName AS SELECT * FROM OPENQUERY(Yourservername,''EXECUTE [databasename].[dbo].[sp_name] ''''' +Parameter + ''''''')')

answered Mar 8, 2019 at 6:53

Balamurugan

12 bronze badges

1 Comment

Stefan Over a year ago

This answer was flagged as low-quality automatically. Some additional explanation as well as the code would be a big help to readers trying to understand what this does. Thanks!

Collectives™ on Stack Overflow

SQL Server / Create view from stored procedure

5 Answers 5

Comments

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related