这是查询月收入的销售总额和商品的销售总量.
Vendition(销售表)
Vno 销售编号
Bno 商品号(同一类商品编号一样)
Vprice 销售价格
Vcount 每个商品号的销售单价
Vtime 销售时间现在要查询月收入的销售总额和商品的销售总量.请问该如何定义存储过程.
下面的代码是我自己写的.CREATE PROCEDURE query_vendition_via_month_sp
@vtime DATETIME
AS
SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE Vtime = @vtime + '%'
GO
Vendition(销售表)
Vno 销售编号
Bno 商品号(同一类商品编号一样)
Vprice 销售价格
Vcount 每个商品号的销售单价
Vtime 销售时间现在要查询月收入的销售总额和商品的销售总量.请问该如何定义存储过程.
下面的代码是我自己写的.CREATE PROCEDURE query_vendition_via_month_sp
@vtime DATETIME
AS
SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE Vtime = @vtime + '%'
GO
CREATE PROCEDURE query_vendition_via_month_sp
@vtime DATETIME
AS SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE year(Vtime) = year(@vtime) and month(Vtime)=month(@vtime)
GO
SET @vtime = 2009-06-30;
EXECUTE dbo.query_withdraw_sp @vtime
--try
EXECUTE dbo.query_withdraw_sp ‘2009-06-30’
EXECUTE dbo.query_withdraw_sp '2009-06-30'说明没有适合的数据!呵呵
CREATE PROCEDURE query_vendition_via_month_sp
@vtime DATETIME
AS
SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE year(Vtime) = year(@vtime) and month(Vtime) = month(@vtime)
and day(Vtime) = day(@vtime)
GO
换个思路试试
将参数改为其实日期和结束日期两个更好,效率更高,灵活性更高。查询使用
Where Between Vtime @Date1 And @Date2
Where Vtime Between @Date1 And @Date2
CREATE PROCEDURE query_vendition_via_month_sp
@vtime DATETIME
AS
SELECT SUM(Vprice*Vcount) as '销售总额',
SUM(Vcount) as '销售总量'
FROM Vendition WHERE year(Vtime) = year(@vtime) and month(Vtime) = month(@vtime)
and day(Vtime) = day(@vtime)
GO漏了两个AS和',补上
可是执行了EXECUTE dbo.query_vendition_via_month_sp '2009-06'后
出现消息 8114,级别 16,状态 1,过程 query_vendition_via_month_sp,第 0 行
从数据类型 varchar 转换为 datetime 时出错。
怎么办?其实查询的就是月销售,并不是日销售.
我想执行的是输入年月就显示月销售.
EXECUTE dbo.query_vendition_via_month_sp '2009-06-01'
我想执行的是输入年月就显示月销售. if exists (select 1 from dbo.sysobjects where name='query_vendition_via_month_sp' and type='P')
CREATE PROCEDURE query_vendition_via_month_sp
@year tinyint,
@month tinyint,
AS SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE year(Vtime) = @year and month(Vtime)=@month
GO
--exec query_vendition_via_month_sp 2009,6
--这样是不是更好呢
CREATE PROCEDURE query_vendition_via_month_sp
@year tinyint,
@month tinyint
AS SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE year(Vtime) = @year and month(Vtime)=@month
GO
--exec query_vendition_via_month_sp 2009,6
--多写了个 “,” 这样才对
消息 8114,级别 16,状态 5,过程 query_vendition_via_month_sp,第 0 行
从数据类型 int 转换为 tinyint 时出错。
if exists (select 1 from dbo.sysobjects where name='query_vendition_via_month_sp' and type='P')
CREATE PROCEDURE query_vendition_via_month_sp
@year int,
@month int
AS SELECT SUM(Vprice*Vcount) 销售总额,
SUM(Vcount) 销售总量
FROM Vendition WHERE year(Vtime) = @year and month(Vtime)=@month
GO
--exec query_vendition_via_month_sp 2009,6
--这样再试试