22,210
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('[dbo].[vipxs]') IS NOT NULL
DROP PROC [dbo].[vipxs]
GO
CREATE PROC [dbo].[vipxs]
@vip_name VARCHAR(MAX), --客户名称
@startdate datetime , --开始日期
@enddate datetime --结束日期
AS
BEGIN
DECLARE @Sql VARCHAR(MAX) SET @Sql=''
DECLARE @Where VARCHAR(MAX) SET @Where=' '
SET @Where=@Where+' vip_name IN ('+@vip_name+')'
SET @Sql='
select vip_name,checkdate,cash into #t from vipxs
where checkdate>=@startdate and checkdate<=@enddate
select vip_name,sum(cash) "cash"
from #t
where '+@Where+'
group by vip_name
drop table #t
'
--带参数只能用 sp_executesql
EXEC sp_executesql @Sql,N'@startdate datetime,@enddate datetime', @startdate, @enddate
END
GO
--
exec vipxs '''lily'',''jack''','2018-10-01 00:00:00','2018-10-20 00:00:00'
--在 tempdb 库中进行测试
USE tempdb
GO
IF OBJECT_ID('vipxs') IS NOT NULL
DROP TABLE vipxs
GO
CREATE TABLE vipxs(
vip_name NVARCHAR(20),
checkdate DATETIME,
cash DECIMAL(10,2)
)
GO
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('lily','2018-09-01',20);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('lily','2018-10-01',10);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('lily','2018-10-02',30);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('jack','2018-10-03',20);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('jack','2018-10-04',40);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('jack','2018-10-21',20);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('tom','2018-09-05',20);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('tom','2018-10-19',20);
INSERT INTO vipxs(vip_name,checkdate,cash) VALUES('tom','2018-10-21',20);
GO
---------- 以上为测试表及测试数据 ----------------
--存储过程名,以 proc_ 开头比较好, 不要和表名一致,避免产生不必要的误会
IF OBJECT_ID('[dbo].[proc_vipxs]') IS NOT NULL
DROP PROC [dbo].proc_vipxs
GO
CREATE PROC [dbo].proc_vipxs
@vip_name VARCHAR(MAX), --客户名称
@startdate datetime , --开始日期
@enddate datetime --结束日期
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX) --必须为NVARCHAR
SET @Sql=''
DECLARE @Where NVARCHAR(MAX)
SET @Where=' '
SET @Where=@Where+' vip_name IN ('+@vip_name+')'
SET @Sql='
select vip_name,checkdate,cash into #t from vipxs
where checkdate>=@startdate and checkdate<=@enddate
select vip_name,sum(cash) "cash"
from #t
where '+@Where+'
group by vip_name
drop table #t
'
--
EXEC sp_executesql @Sql,N'@startdate datetime,@enddate datetime',@startdate, @enddate
END
GO
--
exec proc_vipxs '''lily'',''jack''','2018-10-01 00:00:00','2018-10-20 00:00:00'