'Create VIEW' 必须是批处理中的唯一语句 [英] 'Create VIEW' must be the only statement in the batch

问题描述

我有以下 SQL:

 ALTER PROCEDURE [dbo].[usp_gettasks]@ID varchar(50)作为声明@PDate 日期而 (DATEPART(DW, @PDate) = 1 或 DATEPART(DW, @PDate) = 7 )开始设置@PDate = DATEADD(day, 1, @PDate)结尾创建视图 tblList AS从 tblLine tt 中选择 tt.ItemOrder,tt.DisplayVal, DATEADD(day, tt.DaysDue, @PDate)其中 tt.ID = 1

我收到以下消息:

<块引用>

不正确的语法:'Create VIEW' 必须是批处理中的唯一语句

我尝试将GO放在Create View之前，但是它无法识别PDate的值.

解决方案

要在存储过程中创建视图，您需要在动态 SQL 中执行此操作(特别是因为视图本身不能接受变量).

>

DECLARE @sql NVARCHAR(MAX);SET @sql = '创建视图 dbo.tblList作为选择项目订单，显示值，SomeAlias = DATEADD(DAY, DaysDue, ''' + CONVERT(CHAR(8), @PDate, 112)+ ''') FROM dbo.tblLine WHERE ID = 1;';EXEC sp_executesql @sql;

但是一旦你第二次调用这个存储过程，它就会失败，因为你试图创建一个名为 dbo.tblList 的视图并且该视图已经存在.也许您可以在比我想在存储过程中创建视图"更高的级别上详细说明您要尝试的内容.

I have the following SQL:

    ALTER PROCEDURE [dbo].[usp_gettasks]  
    @ID varchar(50)

    AS
     declare @PDate Date


     WHILE (DATEPART(DW, @PDate) =  1 OR DATEPART(DW, @PDate) =  7 )
     BEGIN

      set @PDate =  DATEADD(day, 1, @PDate)

     END

     CREATE VIEW tblList AS

     select tt.ItemOrder,tt.DisplayVal,  DATEADD(day, tt.DaysDue, @PDate)  from tblLine tt
     where tt.ID = 1 

I get the following message:

Incorrect syntax: 'Create VIEW' must be the only statement in the batch

I tried putting GO before Create View, but then it can't recognize the value of PDate.

解决方案

To create a view in a stored procedure, you need to do this in dynamic SQL (especially since the view itself can't take a variable).

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'CREATE VIEW dbo.tblList 
    AS
      SELECT ItemOrder, DisplayVal, 
        SomeAlias = DATEADD(DAY, DaysDue, ''' + CONVERT(CHAR(8), @PDate, 112)
      + ''') FROM dbo.tblLine WHERE ID = 1;';
EXEC sp_executesql @sql;

But once you call this stored procedure a second time, it's going to fail, because you are trying to create a view named dbo.tblList and that view already exists. Perhaps you can elaborate on what you're trying to, at a higher level than "I want to create a view in a stored procedure."

这篇关于'Create VIEW' 必须是批处理中的唯一语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！