locked
syntax err in creating view RRS feed

  • Question

  • View is getting created independently but when if i run it in block it giving error.

    Incorrect syntax near the keyword 'view'.

    begin if object_id('dbo.vw_view1', 'v') is not null begin drop view dbo.view1 end else begin create view [dbo].[view1] as select orgid,rpi,Tn,PId,Base,Performance,IReg ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp from MIPStg1 union all select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp from MIPStg2 end end



    Neil

    Wednesday, October 24, 2018 12:25 PM

Answers

  • You can't have GO an a SP, since end-of-batch is also the end of the SP. Only way is to create the view using dynamic SQL, which is a mess. So I suggest that you re-think your approach.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:40 PM
    Wednesday, October 24, 2018 1:28 PM
  • Will this work for you ?

     
    	if object_id('dbo.vw_view1', 'v') is not null
    		begin
    			drop view dbo.view1
    		end
    	GO  
    		 	
    			create view [dbo].[view1] as
    			select 
    				 orgid,rpi,Tn,PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg1
    			union all
    			select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg2
    		 
     


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

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:30 PM
  • Hello Neil,

    The CREATE VIEW Statement must be the first in a Batch, you have to rewrite it like

    	if object_id('dbo.vw_view1', 'v') is not null
    		begin
    			drop view dbo.view1
    		end
    	GO
    		begin	
    			create view [dbo].[view1] as
    			select 
    				 orgid,rpi,Tn,PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg1
    			union all
    			select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg2
    		end
    
    



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Tom Phillips Wednesday, October 24, 2018 1:00 PM
    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:33 PM
  • I think this may be what you're after

    if object_id('dbo.vw_view1', 'v') is not null
    drop view dbo.view1
    go
    
    
    create view [dbo].[view1] 
    as Select orgid,rpi,Tn,PId,Base,Performance,IReg
    ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    from MIPStg1 union all select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp from MIPStg2


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:37 PM
  • Your problem is that you try to combine CREATE VIEW with other commands in the same batch. That is not allowed.

    Also, there are more modern constructs in recent versions of SQL Server. DROP IF EXISTS and CREATE OR ALTER:

    -- Below was introduced in SQL Server 2016
    DROP VIEW IF EXISTS myView
    GO
    
    CREATE VIEW myView AS SELECT 1 AS x
    GO
    
    -- Below was introduced in SQL Server 2016 sp1
    CREATE OR ALTER VIEW myView AS SELECT 1 AS x
    GO


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:40 PM
    Wednesday, October 24, 2018 12:58 PM
  • Hi Tibor,

    I created in dynamic sql and it worked.

    I kept the drop if exists outside the dynamic sql at the beginning of sp.

    Also I am keeping it in sp as they are around 5-10 views which needs to be executed in order. instead of creating separate file I thought of keep them in sp.  

    Thanks!


    Neil

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:41 PM
    Wednesday, October 24, 2018 1:45 PM

All replies

  • Will this work for you ?

     
    	if object_id('dbo.vw_view1', 'v') is not null
    		begin
    			drop view dbo.view1
    		end
    	GO  
    		 	
    			create view [dbo].[view1] as
    			select 
    				 orgid,rpi,Tn,PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg1
    			union all
    			select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg2
    		 
     


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

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:30 PM
  • Hello Neil,

    The CREATE VIEW Statement must be the first in a Batch, you have to rewrite it like

    	if object_id('dbo.vw_view1', 'v') is not null
    		begin
    			drop view dbo.view1
    		end
    	GO
    		begin	
    			create view [dbo].[view1] as
    			select 
    				 orgid,rpi,Tn,PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg1
    			union all
    			select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    				,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    			from MIPStg2
    		end
    
    



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Tom Phillips Wednesday, October 24, 2018 1:00 PM
    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:33 PM
  • I think this may be what you're after

    if object_id('dbo.vw_view1', 'v') is not null
    drop view dbo.view1
    go
    
    
    create view [dbo].[view1] 
    as Select orgid,rpi,Tn,PId,Base,Performance,IReg
    ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp
    from MIPStg1 union all select orgid,rpi,Tn,NULL as PId,Base,Performance,IReg
    ,PHRBonus,Score,ciscore,cbonus,merrordata,MIPsYear,vbonus,regp from MIPStg2


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:39 PM
    Wednesday, October 24, 2018 12:37 PM
  • Your problem is that you try to combine CREATE VIEW with other commands in the same batch. That is not allowed.

    Also, there are more modern constructs in recent versions of SQL Server. DROP IF EXISTS and CREATE OR ALTER:

    -- Below was introduced in SQL Server 2016
    DROP VIEW IF EXISTS myView
    GO
    
    CREATE VIEW myView AS SELECT 1 AS x
    GO
    
    -- Below was introduced in SQL Server 2016 sp1
    CREATE OR ALTER VIEW myView AS SELECT 1 AS x
    GO


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:40 PM
    Wednesday, October 24, 2018 12:58 PM
  • I am placing this code in SP I have multiple views in the sp in similar fashion... now I am getting error at go.

    I also tried the below in SP it is throwing err

    DROP VIEW IF EXISTS vv_view1 

    GO...

    Incorrect syntax near 'vv_view1'.

    Neil


    • Edited by Neilcse Wednesday, October 24, 2018 1:21 PM
    Wednesday, October 24, 2018 1:17 PM
  • You can't have GO an a SP, since end-of-batch is also the end of the SP. Only way is to create the view using dynamic SQL, which is a mess. So I suggest that you re-think your approach.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:40 PM
    Wednesday, October 24, 2018 1:28 PM
  • I am placing this code in SP


    Why would someone want's to create views within a stored procedure?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 24, 2018 1:40 PM
  • Hi Tibor,

    I created in dynamic sql and it worked.

    I kept the drop if exists outside the dynamic sql at the beginning of sp.

    Also I am keeping it in sp as they are around 5-10 views which needs to be executed in order. instead of creating separate file I thought of keep them in sp.  

    Thanks!


    Neil

    • Marked as answer by Neilcse Wednesday, October 24, 2018 6:41 PM
    Wednesday, October 24, 2018 1:45 PM
  • Hi Tibor,

    I created in dynamic sql and it worked.

    I kept the drop if exists outside the dynamic sql at the beginning of sp.

    Also I am keeping it in sp as they are around 5-10 views which needs to be executed in order. instead of creating separate file I thought of keep them in sp.  

    Thanks!


    Neil

    But that is for a single time requirement right? Is there a need to drop and create view everytime? If not, then there's no need of sp. You can simply wrap all of them inside a sql script file which you can execute it when needed. There's no need to create it as a sp in your database

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, October 25, 2018 5:46 AM