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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值