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

Please see "http://stackoverflow.com/questions/7712702/creating-a-view-using-stored-procedure" — Deniyal Tandel, Apr 24 '13 at 06:44
Learn something every day. Just got pinged by this one, thanks for asking the question already. — Spence, Aug 23 '13 at 22:11

Devart · Accepted Answer · 2013-04-24T07:08:23.293

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

@Devart -- You need to add if exists drop view and then create it. Because when a user calls this sproc multiple times,it will throw error as view alredy exists. — AnandPhadke, Apr 24 '13 at 07:02

AnandPhadke · Answer 2 · 2013-04-24T07:04:40.537

You cant create a view inside a procedure.

SO you can first create a view separately like below:

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',' ','');

Then use this view in procedure as per your requirement

OR Using 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'','' '','''');'

IF EXISTS(select * from sys.objects where name='Cust_Address_View')
drop view Cust_Address_View 
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

It can be created using `Dynamic query` .http://stackoverflow.com/questions/7712702/creating-a-view-using-stored-procedure.But anyways it's a bad idea — praveen, Apr 24 '13 at 06:43

score 1 · Answer 3 · edited May 23 '17 at 11:48

1

Hints: Direct create view instead of using store procedure.

Create view using store procedure

edited May 23 '17 at 11:48

Community

1
1

answered Apr 24 '13 at 06:50

Deniyal Tandel

512
3
14

Incorrect syntax near the keyword 'VIEW'

3 Answers3