ALTER VIEW/Stored Procedure Problem

I'm getting a "Syntax Error Near Keyword View" Error when I check the syntax of this Stored Procedure.  Can anyone tell me why?  Thank you so much!

Brian

CREATE PROCEDURE spGenerateReportData

      @StartTimeWindow DATETIME,
      @EndTimeWindow DATETIME,
      @Line varChar,
      @LineStatus varChar,
      @CauseType varChar,
      @Shift varChar,
      @Team varChar,
      @Lot varChar,
      @Product varChar

AS
BEGIN
IF EXISTS (SELECT NAME FROM objSystems WHERE NAME = 'qrySearchCriteria1')
ALTER VIEW qrySearchCriteria1 (Process_Area, Machine, Unit_Op, Auto_Cause, Occurrences)
      AS
      SELECT Process_Area, Machine, Unit_Op, Auto_Cause, Count(Start_Time) AS Occurrences
      FROM qrytblMachineDT
      WHERE (Start_DateTime > @StartTimeWindow) AND (Start_DateTime <= @EndTimeWindow)
      AND (Line IN (@Line)) AND (Line_Status IN (@LineStatus)) AND (Cause_Type IN (@CauseType))
      AND (Shift IN (@Shift)) AND (Team IN (@Team)) AND (Lot IN (@Lot)) AND (Product IN (@Product))
      GROUP BY Process_Area, Machine, Unit_Op, Auto_Cause
END
LVL 1
beercat2Asked:
Who is Participating?
 
wqwConnect With a Mentor Commented:
no DDL allowed in stored procedures. sorry. here is my solution:

create table t1 (c1 int, c2 int)

create view v1 as select c1 from t1

create proc a
as
exec ('alter view v1 as select c2 from t1')

exec a

exec sp_helptext v1

Just wrap your DDL language in exec statement.

HTH,

</wqw>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.