Geek asks and answers

"Create VIEW" must be the only expression in the package

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:

Invalid syntax: “Create VIEW” must be the only one in the package

I tried to put GOup Create View, but then it cannot recognize the meaning PDate.

+5
source share
1 answer

To create a view in a stored procedure, you need to do this in dynamic SQL (especially since the view itself cannot accept a variable).

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

, , dbo.tblList, . , , , , " ".

+10

More articles:


All Articles