May 12, 2010 at 3:53 am
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
May 12, 2010 at 4:40 am
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.
May 12, 2010 at 4:49 am
It worked ,,, thankx...:-)
Cheers
May 12, 2010 at 6:27 am
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
May 12, 2010 at 6:30 am
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
May 12, 2010 at 6:35 am
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.
May 12, 2010 at 6:38 am
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
May 12, 2010 at 6:43 am
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
May 12, 2010 at 6:49 am
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.
May 12, 2010 at 7:15 am
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
May 12, 2010 at 10:55 am
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
May 12, 2010 at 12:25 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply