Incorrect syntax near the keyword 'VIEW'

Question

I am getting the below error when i run the below writtend procedure. What goes wrong with this?

Error :

Msg 156, Level 15, State 1, Procedure tbv21Setup, Line 11 Incorrect syntax near the keyword 'VIEW'.

**

CREATE PROCEDURE tbv21Setup

AS
BEGIN

    CREATE VIEW Cust_Address_View AS
        SELECT customer.individual_id   individual_id,
          customer.tb_customer_id   customer_id,
          customer.customer_category_lkp    customer_category_lkp,
          ADDRESS.postal_code   postal_code
        FROM CUSTOMER
        INNER JOIN INDIVIDUAL
        ON (CUSTOMER.individual_id = INDIVIDUAL.individual_id)
        JOIN
          (SELECT address_id ,
            a.individual_id ,
            postal_code ,
            obsolete_flag ,
            tb_modified_date
          FROM address a
          INNER JOIN
            (SELECT individual_id,
              MAX(ISNULL((tb_modified_date),('01/01/1900'))) AS max_tb_modified_date
            FROM ADDRESS
            WHERE obsolete_flag = 0
            GROUP BY individual_id
            ) AS xa
          ON (xa.individual_id                   = a.individual_id
          AND xa.max_tb_modified_date            = ISNULL((a.tb_modified_date),('01/01/1900')))
          WHERE a.obsolete_flag                  = 0
          ) AS ADDRESS ON (ADDRESS.individual_id = INDIVIDUAL.individual_id)
        AND customer.customer_category_lkp       =
          (SELECT lookup_id
          FROM lookup
          WHERE lookup_category_name = 'customer_category'
          AND code_string            ='prospect'
          )
        AND customer.obsolete_flag   =0
        AND ADDRESS.postal_code NOT IN ('UNKNOWN','unknown','U','u',' ','');

    ALTER TABLE tb_customer_attribute 
    ADD  tb_customer_attribute_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
                obsolete_flag int,
                revision_number int;

    CREATE TABLE tb_comm_hist_xfer (
        tb_comm_hist_xfer_id binary(16) NOT NULL,
        tb_old_customer_id int NOT NULL,
        tb_customer_id int NULL,
        date_entered datetime NOT NULL
    );


    --Logger table
    CREATE TABLE TBLogger (logDate DATETIME, logSQL VARCHAR(MAX), logRows INT, errKode INT);

    --Create a parameter table in CRB/STAGE database which will hold a row for crb/stage db prefix.
    CREATE TABLE crb_parameter (stgdb_prefix VARCHAR(255));

    INSERT INTO crb_parameter values ('tommydev1.dbo');
    --uncomment the below line and comment the above line in PRODUCTION
    --INSERT INTO crb_parameter values ('tb_stage.dbo');


END

Answer 1

Can't create DDL inside sp, only in batch. Otherwise try dynamic SQL -

CREATE PROCEDURE tbv21Setup

AS
BEGIN

    EXEC sp_executesql N'

    CREATE VIEW Cust_Address_View AS
        SELECT customer.individual_id   individual_id,
          customer.tb_customer_id   customer_id,
          customer.customer_category_lkp    customer_category_lkp,
          ADDRESS.postal_code   postal_code
        FROM CUSTOMER
        INNER JOIN INDIVIDUAL
        ON (CUSTOMER.individual_id = INDIVIDUAL.individual_id)
        JOIN
          (SELECT address_id ,
            a.individual_id ,
            postal_code ,
            obsolete_flag ,
            tb_modified_date
          FROM address a
          INNER JOIN
            (SELECT individual_id,
              MAX(ISNULL((tb_modified_date),(''01/01/1900''))) AS max_tb_modified_date
            FROM ADDRESS
            WHERE obsolete_flag = 0
            GROUP BY individual_id
            ) AS xa
          ON (xa.individual_id                   = a.individual_id
          AND xa.max_tb_modified_date            = ISNULL((a.tb_modified_date),(''01/01/1900'')))
          WHERE a.obsolete_flag                  = 0
          ) AS ADDRESS ON (ADDRESS.individual_id = INDIVIDUAL.individual_id)
        AND customer.customer_category_lkp       =
          (SELECT lookup_id
          FROM lookup
          WHERE lookup_category_name = ''customer_category''
          AND code_string            =''prospect''
          )
        AND customer.obsolete_flag   =0
        AND ADDRESS.postal_code NOT IN (''UNKNOWN'',''unknown'',''U'',''u'','' '','''');'

EXEC sp_executesql N'

    ALTER TABLE tb_customer_attribute 
    ADD  tb_customer_attribute_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
                obsolete_flag int,
                revision_number int;

    CREATE TABLE tb_comm_hist_xfer (
        tb_comm_hist_xfer_id binary(16) NOT NULL,
        tb_old_customer_id int NOT NULL,
        tb_customer_id int NULL,
        date_entered datetime NOT NULL
    );

    CREATE TABLE TBLogger (logDate DATETIME, logSQL VARCHAR(MAX), logRows INT, errKode INT);

    CREATE TABLE crb_parameter (stgdb_prefix VARCHAR(255));

    INSERT INTO crb_parameter VALUES (''tommydev1.dbo'');'


END

UPDATE:

CREATE PROCEDURE tbv21Setup

AS
BEGIN

    EXEC sp_executesql N'

    IF EXISTS(
        SELECT 1 
        FROM dbo.sysobjects 
        WHERE id = OBJECT_ID(''dbo.Cust_Address_View'')
    ) DROP VIEW dbo.Cust_Address_View;

    CREATE VIEW dbo.Cust_Address_View AS
        SELECT customer.individual_id   individual_id,
          customer.tb_customer_id   customer_id,
          customer.customer_category_lkp    customer_category_lkp,
          ADDRESS.postal_code   postal_code
        FROM CUSTOMER
        INNER JOIN INDIVIDUAL
        ON (CUSTOMER.individual_id = INDIVIDUAL.individual_id)
        JOIN
          (SELECT address_id ,
            a.individual_id ,
            postal_code ,
            obsolete_flag ,
            tb_modified_date
          FROM address a
          INNER JOIN
            (SELECT individual_id,
              MAX(ISNULL((tb_modified_date),(''01/01/1900''))) AS max_tb_modified_date
            FROM ADDRESS
            WHERE obsolete_flag = 0
            GROUP BY individual_id
            ) AS xa
          ON (xa.individual_id                   = a.individual_id
          AND xa.max_tb_modified_date            = ISNULL((a.tb_modified_date),(''01/01/1900'')))
          WHERE a.obsolete_flag                  = 0
          ) AS ADDRESS ON (ADDRESS.individual_id = INDIVIDUAL.individual_id)
        AND customer.customer_category_lkp       =
          (SELECT lookup_id
          FROM lookup
          WHERE lookup_category_name = ''customer_category''
          AND code_string            =''prospect''
          )
        AND customer.obsolete_flag   =0
        AND ADDRESS.postal_code NOT IN (''UNKNOWN'',''unknown'',''U'',''u'','' '','''');'

EXEC sp_executesql N'

    ALTER TABLE dbo.tb_customer_attribute 
    ADD  tb_customer_attribute_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
                obsolete_flag int,
                revision_number INT;

    IF EXISTS(
        SELECT 1 
        FROM dbo.sysobjects 
        WHERE id = OBJECT_ID(''dbo.tb_comm_hist_xfer'')
    ) DROP TABLE dbo.tb_comm_hist_xfer;

    CREATE TABLE dbo.tb_comm_hist_xfer (
        tb_comm_hist_xfer_id binary(16) NOT NULL,
        tb_old_customer_id int NOT NULL,
        tb_customer_id int NULL,
        date_entered datetime NOT NULL
    );

    IF EXISTS(
        SELECT 1 
        FROM dbo.sysobjects 
        WHERE id = OBJECT_ID(''dbo.TBLogger'')
    ) DROP TABLE dbo.TBLogger;

    CREATE TABLE dbo.TBLogger (logDate DATETIME, logSQL VARCHAR(MAX), logRows INT, errKode INT);

    IF EXISTS(
        SELECT 1 
        FROM dbo.sysobjects 
        WHERE id = OBJECT_ID(''dbo.crb_parameter'')
    ) DROP TABLE dbo.crb_parameter;

    CREATE TABLE dbo.crb_parameter (stgdb_prefix VARCHAR(255));

    INSERT INTO dbo.crb_parameter VALUES (''tommydev1.dbo'');'

END