'Create VIEW' must be the only statement in the batch

I have teh following:

    ALTER PROCEDURE [dbo].[usp_gettasks]  
    @ID varchar(50)

     declare @PDate Date

     WHILE (DATEPART(DW, @PDate) =  1 OR DATEPART(DW, @PDate) =  7 )

      set @PDate =  DATEADD(day, 1, @PDate)


     CREATE VIEW tblList AS

     select tt.ItemOrder,tt.DisplayVal,  DATEADD(day, tt.DaysDue, @PDate)  from tblLine tt
     where tt.ID = 1 

I get the following message:
Incorrect syntax: ‘Create VIEW’ must be the only statement in the batch

  • Connect a C# MVC application to a remote SQL Server database
  • select query on one table with join on same table
  • Why is the addition operator defined for DATETIME values but not for DATE?
  • PIVOT dynamically, Returned results from JOIN of two tables
  • Moving records up and down with Linq to SQL
  • Create a Pie Chart from single row, multiple column dataset in SSRS
  • I put GO before Create View but then it can't recognize the value of PDate

  • How to know if a field is numeric in Linq To SQL
  • How to properly index a many-many association table?
  • iPhone Sdk can Access SQL Server Directly
  • SQL_Latin1_General_CP1_CS_AS vs Latin1_General_CS_AS
  • t-sql insert - select - with parameters
  • SSIS Package not wanting to fetch metadata of temporary table
  • One Solution collect form web for “'Create VIEW' must be the only statement in the batch”

    To create a view in a stored procedure, you need to do this in dynamic SQL (especially since the view itself can’t take a variable).

    SET @sql = 'CREATE VIEW dbo.tblList 
          SELECT ItemOrder, DisplayVal, 
            SomeAlias = DATEADD(DAY, DaysDue, ''' + CONVERT(CHAR(8), @PDate, 112)
          + ''') FROM dbo.tblLine WHERE ID = 1;';
    EXEC sp_executesql @sql;

    But once you call this stored procedure a second time, it’s going to fail, because you are trying to create a view named dbo.tblList and that view already exists. Perhaps you can elaborate on what you’re trying to, at a higher level than “I want to create a view in a stored procedure.”

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.