none
Getting incorrect syntax near the keyword 'ELSE' in sp to drop and rebuild table if exists else rebuild table

    General discussion


  • ALTER PROCEDURE [dbo].[spUpdate_K_XX]
    AS
    BEGIN
    IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'XXXXXXX' 
                     AND  TABLE_NAME = 'K_XX'))

        drop table [K_XX]
    SET NOCOUNT ON;

    SELECT   * into [K_XXX] from openquery(GHWPRD,'select  MPRL_CODE,MPROF_CODE,RCD_DEL_IND,RCD_END_DATE,
    RCD_START_DATE,MPRL_SEQ,HOSP_CODE FROM         K_XXX')
    END
    ELSE
    BEGIN
    SET NOCOUNT ON;

    SELECT   * into [K_XXX] from openquery(GHWPRD,'select  MPRL_CODE,MPROF_CODE,RCD_DEL_IND,RCD_END_DATE,
    RCD_START_DATE,MPRL_SEQ,HOSP_CODE FROM         K_XXXX')
    END

    Thursday, September 12, 2013 12:03 AM

All replies

  • Your statement is hard to read due to formatting, but you have your BEGIN and END statements mismatched

    ALTER PROCEDURE [dbo].[spUpdate_K_XX] 
    AS
     BEGIN
     IF (EXISTS (SELECT * 
                      FROM INFORMATION_SCHEMA.TABLES 
                      WHERE TABLE_SCHEMA = 'XXXXXXX' 
                      AND  TABLE_NAME = 'K_XX'))
     BEGIN
        drop table [K_XX]
     SET NOCOUNT ON;
    
     
    SELECT   * into [K_XXX] from openquery(GHWPRD,'select  MPRL_CODE,MPROF_CODE,RCD_DEL_IND,RCD_END_DATE,
     RCD_START_DATE,MPRL_SEQ,HOSP_CODE FROM         K_XXX')
     END
     ELSE
     BEGIN
     SET NOCOUNT ON;
    
     
    SELECT   * into [K_XXX] from openquery(GHWPRD,'select  MPRL_CODE,MPROF_CODE,RCD_DEL_IND,RCD_END_DATE,
     RCD_START_DATE,MPRL_SEQ,HOSP_CODE FROM         K_XXXX')
     END
    END


    Thursday, September 12, 2013 12:42 AM
  • For each IF or ELSE condition , if BEGIN is used then there should an END as well.

    Also, in case of ELSE , if IF is having multiple statements , then group them using BEGIN and END


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 12:54 AM
  • Thanks  ReportCreator, that did the job.
    Thursday, September 12, 2013 1:03 AM