how do i create a view within a procedure that uses a variable defined in the procedur

regard

What would be the point of such an endeavor? Please post your overall
requirements & someone may be able to point to the right direction.

In absolutely no data management contexts does a real need arise to create a
view dynamically within a stored procedure.

--
Anith

Hi,

Use the dynamic sql to create the view using a variable inside procedure.

The same procedure is given below. Ensure that you drop the view every time,
otherwise
procedure execution will give an error stating that view already exists.

Create procedure cr_view
as
begin
declare @sql nvarchar(1000)
declare @v_name varchar(30)
set @v_name='hari_view'
set @sql = 'create view '+@v_name+ ' as select * from sysobjects'
exec sp_executesql @sql
end

Thanks
Hari
MCDBA
[quoted text, click to view]

Anith,

[quoted text, click to view]

You need to get out more. :)
Of course there is a real need in the real world. Partitioned views
come to mind immediately.

Our daily pipeline process creates hourly, daily, weekly, and
monthly partitions as needed. Every day the partitioned views need
to be dropped and recreated to include the new partitions. We have
several hundred partitioned views in our database. The whole process
is automated. Tables and view are created at runtime by stored
procedures. Without this automation, the whole process would be
unmanageable.

Linda

Linda,

[quoted text, click to view]

Well, apparently I have gotten out well enough not to confuse data
management with scheduled creation of tables and/or views :-)

[quoted text, click to view]
to mind immediately. <<

Dealing with data sources external to the DBMS or certain specialized data
processing schemes may require some amount of automation and organizations
use various ways to accomodate these processes with procedures creating &
destroying ad hoc tables, view etc. Even a simple DTS process that creates a
table or meta-data related procedures for that matter would be such an
instance.

Categorizing all such processes and workarounds as relational data
management is like labeling all that glitters as gold. Data management in
relational databases involves imposing structural, manipulative and
integrity dictums for logical data representation.

And that is exactly why I asked the OP to post his overall requirements
rather than what he perceived as an immediate need.

--
Anith