CREATE VIEW must be the only statement in a batch ?

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 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 above was does not 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