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

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 06/07/04 

Date: Mon, 7 Jun 2004 14:52:41 +0530

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
"Nikhil" <anonymous@discussions.microsoft.com> wrote in message
news:4D6D678B-C28B-4300-91DC-6413AD3C3BA9@microsoft.com...
> how do i create a view within a procedure that uses a variable defined in
the procedure
>
> regards
>
> nikhil

Relevant Pages

  • Re: Using variables in Dynamic SQL
    ... > execute dynamic SQL I am getting the error message "Syntax error ... > DECLARE @StartDate datetime ... > So my 2nd query is can anybody advise me of the correct format here. ...
    (microsoft.public.sqlserver.programming)
  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • Re: using sp_executesql for dynamic search in stored procedure.
    ... I assume you had some quotes around the dynamic SQL and parameter list? ... DECLARE @int_start AS INTEGER ... SET @int_start = 1--this variable will be read into the stored procedure. ...
    (microsoft.public.sqlserver.server)
  • Re: Performance Issues With Query
    ... SQL Server MVP ... > DECLARE @Timestamp datetime ... > DECLARE @CallEventName varchar ... > UPDATE CallByCallYesterdayFinal SET Script = SUBSTRING(@Destination, ...
    (microsoft.public.sqlserver)
  • Re: Problem with SQL UDF
    ... DECLARE RLIB VARCHAR; ... DECLARE RVAR CHARDEFAULT ' '; ... In SQL reference, I found that, if not explicitly declared in a declare procedure statement, all arguments inside a variable will be considered of type INOUT, so I assumed that should run as is, but it's not. ...
    (comp.sys.ibm.as400.misc)