Is it possible to create a view within a stored procedure,'CREATE VIEW' must be the first statement in a query batch.

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

-refer to http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/af8b0c2d-8117-42e3-a701-05035644f73d

 

 

Solutions :

 

ALTER PROCEDURE [dbo].[prc_TEST]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;
    --SET ANSI_NULLS ON
    --SET QUOTED_IDENTIFIER ON
    DECLARE @sql NVARCHAR(max)
    SELECT @sql='IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[dbo].[IView_LISTING]''))
    DROP VIEW [dbo].[I IView_LISTING ]
    '
    EXEC(@sql);

    SELECT @sql = N'
    CREATE  VIEW [dbo].[IView_LISTING] WITH SCHEMABINDING
    AS
        XXXXXX
    '

    EXEC (@sql);
   
    SELECT @sql=N'CREATE UNIQUE CLUSTERED Index PK_IView_LISTING_FEED ON IView_LISTING
      (FEED_ID);'
   
    EXEC (@sql);
     

END