以下的存储过程,执行后,读到的视图中是没有数据的,如果单独写视图的话,在读取就有数据,这是为什么呢?求解!!!/*动态视图*/create proc MonthSales(@stardate datetime ,@overdate datetime)
as declare @date1 nvarchar(12),@date2 nvarchar(12),@sql varchar(6000)
SELECT @date1=CONVERT(varchar(12), @stardate, 23)
SELECT @date2=CONVERT(varchar(12), @overdate, 23)if exists (select * from dbo.sysobjects where id = object_id(N'MonthSales_view') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
drop view MonthSales_view
end
set @sql='CREATE VIEW MonthSales_view as '+
' SELECT P.UNIT_NUM AS UNIT_NUM,P.UNIT_NAME AS UNIT_NAME,datepart(year, A.DOC_DATE) AS year, datepart(month, A.DOC_DATE) AS MONTH, '+
' jijie=(case when datepart(month, A.DOC_DATE) between 1 and 3 then ''春 ''' +
' when datepart(month, A.DOC_DATE) between 4 and 6 then ''夏 ''' +
' when datepart(month, A.DOC_DATE) between 7 and 9 then ''秋 ''' + ' else ''冬''end ) , ' +
' SUM(K.SELL_QTY) AS SUM_SELL_QTY, SUM(K.SELL_VAL) AS SUM_SELL_VAL, '+
' COUNT(DISTINCT B.UNIT_NUM) AS DD, cast(SUM(K.SELL_QTY)/COUNT(DISTINCT B.UNIT_NUM) as decimal(10,0)) AS PP, '+
' cast(SUM(K.SELL_VAL)/COUNT(DISTINCT B.UNIT_NUM) as decimal(10,2)) AS JJ FROM RLB A '+
' INNER JOIN SYS_UNIT B ON (A.SHOP_ID = B.UNIT_ID) '+
' LEFT OUTER JOIN SYS_UNIT P ON (A.UNIT_ID = P.UNIT_ID) '+
' LEFT OUTER JOIN RLB_DTL K ON (A.SHOP_ID = K.SHOP_ID AND A.RLB_NUM = K.RLB_NUM)'+
' WHERE (B.HIERARCHY LIKE '+''''+ '|1|%'+''''+') AND (A.DOC_DATE BETWEEN '+@date1+' AND '+@date2+' ) GROUP BY datepart(year, A.DOC_DATE),datepart(month, A.DOC_DATE),P.UNIT_NAME, P.UNIT_NUM '
exec(@sql)
if exists (select * from dbo.sysobjects where id = object_id(N'MonthSales_view') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select * from MonthSales_view
endgo
exec MonthSales '20110501','20110601'
as declare @date1 nvarchar(12),@date2 nvarchar(12),@sql varchar(6000)
SELECT @date1=CONVERT(varchar(12), @stardate, 23)
SELECT @date2=CONVERT(varchar(12), @overdate, 23)if exists (select * from dbo.sysobjects where id = object_id(N'MonthSales_view') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
drop view MonthSales_view
end
set @sql='CREATE VIEW MonthSales_view as '+
' SELECT P.UNIT_NUM AS UNIT_NUM,P.UNIT_NAME AS UNIT_NAME,datepart(year, A.DOC_DATE) AS year, datepart(month, A.DOC_DATE) AS MONTH, '+
' jijie=(case when datepart(month, A.DOC_DATE) between 1 and 3 then ''春 ''' +
' when datepart(month, A.DOC_DATE) between 4 and 6 then ''夏 ''' +
' when datepart(month, A.DOC_DATE) between 7 and 9 then ''秋 ''' + ' else ''冬''end ) , ' +
' SUM(K.SELL_QTY) AS SUM_SELL_QTY, SUM(K.SELL_VAL) AS SUM_SELL_VAL, '+
' COUNT(DISTINCT B.UNIT_NUM) AS DD, cast(SUM(K.SELL_QTY)/COUNT(DISTINCT B.UNIT_NUM) as decimal(10,0)) AS PP, '+
' cast(SUM(K.SELL_VAL)/COUNT(DISTINCT B.UNIT_NUM) as decimal(10,2)) AS JJ FROM RLB A '+
' INNER JOIN SYS_UNIT B ON (A.SHOP_ID = B.UNIT_ID) '+
' LEFT OUTER JOIN SYS_UNIT P ON (A.UNIT_ID = P.UNIT_ID) '+
' LEFT OUTER JOIN RLB_DTL K ON (A.SHOP_ID = K.SHOP_ID AND A.RLB_NUM = K.RLB_NUM)'+
' WHERE (B.HIERARCHY LIKE '+''''+ '|1|%'+''''+') AND (A.DOC_DATE BETWEEN '+@date1+' AND '+@date2+' ) GROUP BY datepart(year, A.DOC_DATE),datepart(month, A.DOC_DATE),P.UNIT_NAME, P.UNIT_NUM '
exec(@sql)
if exists (select * from dbo.sysobjects where id = object_id(N'MonthSales_view') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select * from MonthSales_view
endgo
exec MonthSales '20110501','20110601'
AS
DECLARE @sql VARCHAR(1000)
set @sql='CREATE VIEW MonthSales_view as '+
'select * from dbo.Categories'
EXEC(@sql)SELECT * FROM MonthSales_view
go
EXEC usp_cview测试数据库是 northwind 我的是没问题的,我不知道楼主什么情况