Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.