view inside stored procedure

Question

Post reply

view inside stored procedure

AppSup_dba

SSCrazy

Points: 2215
More actions
May 12, 2010 at 3:53 am

#222377

Hi Experts,
How can we create a VIEW inside a Stored Procedure?
I am using the below query:
CREATE PROC USP_VIEW
AS
CREATE VIEW view_name AS SELECT * FROM SSIL_GS1MAS
ERROR:
Msg 156, Level 15, State 1, Procedure USP_VIEW, Line 5
Incorrect syntax near the keyword 'VIEW'.
Cheers

Viewing 12 posts - 1 through 11 (of 11 total)

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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

Hi Ankur,

You can use dynamic sql statements.

Here is a sample script

CREATE PROC USP_VIEW
AS
DECLARE @sql nvarchar(max)
SET @sql = N'CREATE VIEW view_name AS SELECT TOP 3 * FROM Customer'
EXEC sp_executesql @sql
SELECT * FROM view_name
GO

I hope that can solve your problem.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

AppSup_dba SSCrazy Points: 2215 More actions · Answer 2

AppSup_dba

SSCrazy

Points: 2215

May 12, 2010 at 4:49 am

#1166076

It worked ,,, thankx...:-)

Cheers

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

Hi Ankur,

Actually we have to add the DROP VIEW command within the procedure, otherwise a second call to SP will cause an error.

Here is a better version.

CREATE PROC USP_VIEW
AS
DECLARE @sql nvarchar(max)
SET @sql = N'CREATE VIEW view_name AS SELECT TOP 3 * FROM Customer'
EXEC sp_executesql @sql
SELECT * FROM view_name
SET @sql = N'DROP VIEW view_name'
EXEC sp_executesql @sql
GO

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

I have to ask, why do you want to create a view from within a stored procedure? What's the point?

"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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 5

In fact, I can not see any advantage by creating a view within a stored procedure.

But what I'm interested is that the sql engine successfully compiles the sp code without giving any error during the sp creation where there is not any object with view name.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Jonas.SQL SSC Veteran Points: 263 More actions · Answer 6

Then you might wanna do the if exists check before, to make it more usable outside the sp. Unless you just want to use the view during sp execution, of course.

ALTER PROC USP_VIEW

AS

DECLARE @sql nvarchar(max)

SET @sql = N'IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[dbo].[view_name]''))

DROP VIEW [dbo].[view_name]'

EXEC sp_executesql @sql

SET @sql = N'CREATE VIEW view_name AS SELECT TOP 3 * FROM sys.sysobjects'

EXEC sp_executesql @sql

SELECT * FROM view_name

GO

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

Ah, OK.

"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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 8

Hi Grant,

I just realized that you are the author of a book focused on t-sql query performance.

I believe such a usage will affect the performance of SQL Server in a negative way.

BTW, Jonas' sql is a better solution.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

Running data definition language (DDL) statements are generally not a performance tuning consideration, but then, they normally aren't run from within procedures like that. As a matter of fact, it's a pretty abnormal function, which is why I asked about it.

"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

AppSup_dba SSCrazy Points: 2215 More actions · Answer 10

i have to cleanse some data at back-end level, which was earlier done at front. so for that i was considering using a view/ or a temp table as a staging table in a SP. please suggest if i can do above task in a better way..

cleansing here refers grouping etc.

Cheers

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 11

Well a view wouldn't make a good staging table since no data is actually stored. If you must stage the data, I'd use a temporary table unless there were only a few rows or I wasn't planning on joining or searching on it and then I'd use a table variable.

"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