'CREATE VIEW' 必须是批查询中的第一条语句 ？

[0755]

在SQL SERVER 中，'CREATE VIEW' 必须是批查询中的第一条语句。于是下面的语句在查询分析器中老是执行不了，错误提示如下提示：

服务器: 消息 111，级别 15，状态 1，行 10
'CREATE VIEW' 必须是批查询中的第一条语句。



我的程序原句：
Declare @BillDate_From char(8)
Declare @BillDate_To char(8)

Set @BillDate_From='20030501'
Set @BillDate_To='20030509'

Create View vTotalSpent
As
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin

如果真的要在条件语句中用到变量，那么肯定要在create view之前初始化变量，但create view语句又必须为第一条语句。

这个问题应该怎么解决，请大侠们多多指点！ 谢谢！

[Dennis618]

把執行語句放入一個字符變量,再用 exec(@變量)執行
Declare @BillDate_From char(8)
Declare @BillDate_To char(8)
declare @str nvarchar(1000)
Set @BillDate_From='20030501'
Set @BillDate_To='20030509'

set @str='
Create View vTotalSpent
As
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between ' + @BillDate_From'+' and '+@BillDate_To +')
Group by Pin'

exec(@str)
go

[pengdali]

create function fun(@BillDate_From char(8),@BillDate_To char(8))
returns table
as
return(
Select Pin, IsNull(Sum(total_spent),0) Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin)
go

--调用：
select * from dbo.fun('20030501','20030509')

[arrow_gx]

用存储过程解决

[aierong]

Declare @BillDate_From char(8)
Declare @BillDate_To char(8)

Set @BillDate_From='20030501'
Set @BillDate_To='20030509'

Create View vTotalSpent
As
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin

如果真的要在条件语句中用到变量，那么肯定要在create view之前初始化变量，但create view语句又必须为第一条语句。

这个问题应该怎么解决，请大侠们多多指点！ 谢谢！


create function fun(@BillDate_From char(8),@BillDate_To char(8))
returns table
as
return
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin



建议你用函数取代你的试图
select * from dbo.fun('20030501','20030509')

[txlicenhe]

用存储过程或函数
create procedure test
@BillDate_From char(8),@BillDate_To char(8)
as
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin

go
--
Declare @BillDate_From char(8)
Declare @BillDate_To char(8)
Set @BillDate_From='20030501'
Set @BillDate_To='20030509'
exec test @BillDate_From,@BillDate_To

[aierong]

改为存储过程也行

[arrow_gx]

create procedure test
@BillDate_From char(8)
Declare @BillDate_To char(8)
as
Select Pin, IsNull(Sum(total_spent),0) as Total_Spent
From BS_Batch_Daily_CDR_Total
Where (batch_date Between @BillDate_From and @BillDate_To)
Group by Pin

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


然后带参数调用存储过程，参数的赋值在程序里