5

I have the following SQL:

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

    AS
     declare @PDate Date


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

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

     END

     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

I tried putting GO before Create View, but then it can't recognize the value of PDate.

SmokeDetector
  • 751
  • 2
  • 8
  • 12
Nate Pet
  • 44,246
  • 124
  • 269
  • 414

1 Answers1

11

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). This is because modules can not be created as part of a larger script (like one that would be required if you have conditional logic, like IF <some condition> CREATE VIEW).

DECLARE @sql nvarchar(max) = N'CREATE VIEW dbo.tblList 
    AS
      SELECT ItemOrder, DisplayVal, 
        SomeAlias = DATEADD(DAY, DaysDue, ''' 
          + CONVERT(char(8), @PDate, 112)
          + N''') FROM dbo.tblLine WHERE ID = 1;';

EXEC sys.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."

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    He could avoid creation by saying "IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE TABLE_NAME = 'tblList') BEGIN CREATE VIEW ... END". I'm trying to do this, but I get the same error What @NatePet quotes. I srsly need to put the whole statement into a string? Why does ALTER VIEW behave like that? – Csaba Toth Jul 24 '13 at 18:12