create-view-must-be-the-only-statement-in-a-batch

CREATE VIEW must be the only statement in a batch

2011-03-03

In SQL Server, when you try to input a "Create View" sentence in a Stored Procedure. you might get the following error:

CREATE VIEW must be the only statement in a batch

Because CREATE VIEW must be the only statement in a batch, you can use GO commands that are required to isolate the CREATE VIEW statement from the USE and SELECT statements around it.

USE pubs
GO /* Signals the end of the batch */
 
CREATE VIEW auth_titles
AS
SELECT *
FROM authors
GO /* Signals the end of the batch */

SELECT * 
FROM auth_titles
GO /* Signals the end of the batch */

You can try to use the sample shown in this MSDN page

But, all the above methods don't work for me, I am use SQL Server Express 2008 R2.

My original stored procedure was:

CREATE PROCEDURE [dbo].[CreateFeedBackErrorInfoView]
AS

BEGIN
    IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS  
        WHERE TABLE_CATALOG = 'WPRIMARY'
          AND TABLE_SCHEMA = 'dbo'
          AND TABLE_NAME = 'ViewFeedBackErrorInfo'))
         
    BEGIN
        SELECT CREATE VIEW [dbo].[ViewFeedBackErrorInfo] AS
                SELECT   FEEDCfg.MonPtHOSTDBidx,  COUNT(FeedLog.Id) AS FeedErrorCount, SUM(FeedLog.Length) AS FeedErrorLength
                FROM     ABNORMAL_FEEDBACK_CONFIG AS FEEDCfg INNER JOIN
                        ABNORMAL_FEEDBACK_LOG AS FeedLog ON FEEDCfg.CfgAbnormalFeedbackID = FeedLog.CfgAbnormalFeedbackID
                GROUP BY FEEDCfg.MonPtHOSTDBidx
        END
END

The above scripts does NOT work because CREATE VIEW must be the only statement in a batch, and also I could not use Transaction...Commit or GO

Finally, We found a solution: use Dynamic SQL script stored procedure, the sample is the following (Thanks my friend Pandeli's help):

USE [WPRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[CreateFeedBackErrorInfoView]    Script Date: 03/03/2011 11:24:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  ===========================================================
  Author:        
  Create date: <2011 March 3>
  Description:    <[CreateFeedBackErrorInfoView].>
                 <If not create a new one.>
  ===========================================================
CREATE PROCEDURE [dbo].[CreateFeedBackErrorInfoView]
AS
DECLARE @sqlCmd nvarchar (4000)

BEGIN
    IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS   
        WHERE TABLE_CATALOG = 'WPRIMARY' 
          AND TABLE_SCHEMA = 'dbo' 
          AND TABLE_NAME = 'ViewFeedBackErrorInfo'))
          
    BEGIN 
        SELECT @sqlCmd = 'CREATE VIEW [dbo].[ViewFeedBackErrorInfo] AS
                SELECT   FEEDCfg.MonPtHOSTDBidx,  COUNT(FeedLog.Id) AS FeedErrorCount, SUM(FeedLog.Length) AS FeedErrorLength
                FROM     ABNORMAL_FEEDBACK_CONFIG AS FEEDCfg INNER JOIN
                        ABNORMAL_FEEDBACK_LOG AS FeedLog ON FEEDCfg.CfgAbnormalFeedbackID = FeedLog.CfgAbnormalFeedbackID
                GROUP BY FEEDCfg.MonPtHOSTDBidx'
        EXEC sp_executesql @sqlCmd
        END
END