27,580
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[按单统计]
@单据 AS nvarchar(50),
@明细 AS nvarchar(50),
@开始时间 AS datetime = '2007-01-01 00:00:00',
@结束时间 AS datetime = NULL, --怎么调默认为当前时间?
@单据类别 AS nvarchar(50) --默认为任务字符都可以
AS
BEGIN
IF(@结束时间 IS NULL)
SET @结束时间 = GETDATE();
declare @sql nvarchar(1000)
SET @sql = 'SELECT dbo.商品信息.货物编号, ISNULL(统计.数量, 0) AS 数量
FROM (SELECT [dbo].['+@明细+'].货物编号, SUM([dbo].['+@明细+'].数量) AS 数量
FROM [dbo].['+ @单据 +'] INNER JOIN [dbo].['+ @明细+'] ON [dbo].['+ @单据 +'].单号 = [dbo].['+@明细+'].单号
WHERE ([dbo].['+ @单据 +'].通知时间 >= ''' +CONVERT(nvarchar(50),@开始时间, 120)+''')
AND ([dbo].['+ @单据 +'].通知时间 <= '''+ CONVERT(nvarchar(50),@结束时间, 120)+''')
AND (dbo.销售出库单.单据类别 = '''+ CONVERT(nvarchar(50),@单据类别, 120)+''')
GROUP BY [dbo].['+ @明细 +'].货物编号)AS 统计 RIGHT OUTER JOIN
dbo.商品信息 ON 统计.货物编号 = dbo.商品信息.货物编号
ORDER BY dbo.商品信息.排序码'
exec(@sql)
END
ALTER PROCEDURE [dbo].[调用按单统计]
@单据 AS nvarchar(50)= '销售出库单',
@开始时间 AS datetime = '2007-01-01 00:00:00',
@结束时间 AS datetime = NULL, --怎么调默认为当前时间?
@单据类别 AS nvarchar(50) = 'A' --默认为任务字符都可以
AS
declare @i int,@table nvarchar(40),@s nvarchar(3000)
set @i = 1
while @i <= 1
begin
--设置表名变量
select @table = '销售出库单_temp'+convert(char(2),@i)
select @table = @单据 + 'temp'
-------------------------先查是否存在,有的话就先删了再说------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@table+']') AND type in (N'U'))
exec('DROP TABLE [dbo].['+@table+']')
------------------------建空表-------------------------------------------------
select @s = 'select * into '+@table+' from dbo.统计模表 where 1<>1'
exec(@s)
--------------------------写入数据-------------------------------------------
select @s = 'insert into '+@table+' exec [dbo].[按单统计]'+@单据+','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+CONVERT(nvarchar(50),@结束时间, 120)+''','+@单据类别 +''
--exec(@s)
PRINT (@s)--看出来的是什么,复制在查询分析器执行
-------------------------递加变量---------------------------------------------
select @i = @i+1
end
ALTER PROCEDURE 调用按单统计
@单据 AS nvarchar(50)= '销售出库单',
@开始时间 AS datetime = '2007-01-01 00:00:00',
@结束时间 AS datetime = NULL, --怎么调默认为当前时间?
@单据类别 AS nvarchar(50) = 'A' --默认为任务字符都可以
AS
declare @i int,@table nvarchar(40),@s nvarchar(3000)
set @i = 1
while @i <= 20
begin
--设置表名变量-------------------------------------------------------------
select @table = '销售出库单_temp'+convert(char(2),@i
--先查是否存在,有的话就先删了再说-----------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@table+']') AND type in (N'U'))
exec('DROP TABLE [dbo].['+@table+']')
--建空表---------------------------------------------------------------
select @s = 'select * into '+@table+' from dbo.统计模表 where 1<>1'
exec(@s)
--写入数据-------------------------------------------------------------
select @s = 'insert into '+@table+'exec [dbo].[按单统计]'+@单据+','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+CONVERT(nvarchar(50),@结束时间, 120)+''','+@单据类别 +''
exec(@s)
--递加变量-----------
select @i = @i+1
end
GO
ALTER PROCEDURE [dbo].[调用按单统计]
@单据 AS nvarchar(50)= '销售出库单',
@开始时间 AS datetime = '2007-01-01 00:00:00',
@结束时间 AS datetime = NULL, --怎么调默认为当前时间?
@单据类别 AS nvarchar(50) = 'A' --默认为任务字符都可以
AS
declare @i int,@table nvarchar(40),@s nvarchar(3000)
set @i = 1
while @i <= 1
begin
--设置表名变量
select @table = '销售出库单_temp'+convert(char(2),@i)
select @table = @单据 + 'temp'
-------------------------先查是否存在,有的话就先删了再说------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@table+']') AND type in (N'U'))
exec('DROP TABLE [dbo].['+@table+']')
------------------------建空表-------------------------------------------------
select @s = 'select * into '+@table+' from dbo.统计模表 where 1<>1'
exec(@s)
--------------------------写入数据-------------------------------------------
select @s = 'PRINT insert into 销售出库单temp exec [dbo].[按单统计]'+@单据+','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+CONVERT(nvarchar(50),@结束时间, 120)+''','+@单据类别 +''
--exec(@s)
PRINT (@s)
-------------------------递加变量---------------------------------------------
select @i = @i+1
end
--insert into 销售出库汇总
declare @s nvarchar(3000)
declare @table nvarchar(50)
SET @table = '销售出库单_temp'
declare @单据 nvarchar(50)
SET @单据 = N'销售出库单'
declare @开始时间 nvarchar(50)
SET @开始时间 = N'2007-1-1'
declare @结束时间 nvarchar(50)
SET @结束时间 = N'2009-10-25'
declare @单据类别 nvarchar(50)
SET @单据类别 = N'A'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@table+']') AND type in (N'U'))
exec('DROP TABLE [dbo].['+@table+']')
------------------------建空表-------------------------------------------------
select @s = 'select * into '+@table+' from dbo.统计模表 where 1<>1'
exec(@s)
select @s = 'insert into '+@table+' exec [dbo].[按单统计]'+@单据+','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+CONVERT(nvarchar(50),@结束时间, 120)+''','+@单据类别 +''
exec(@s)
--你写得有问题,改成这样
select @s = 'insert into '+@table+' exec [dbo].[按单统计] '''+@单据+''','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+isnull(CONVERT(nvarchar(50),@结束时间, 120),'')+''','''+@单据类别 +''''
select @s = 'insert into '+@table+' exec [dbo].[按单统计]'+@单据+','''+CONVERT(nvarchar(50),@开始时间, 120)+''','''+CONVERT(nvarchar(50),@结束时间, 120)+''','+@单据类别 +''
dbo.student_info 为格式表,你建一个格式一样的
declare @i int,@table nvarchar(40),@s nvarchar(3000)
set @i = 1
while @i <= 20
begin
select @table = '销售出库单_temp'+convert(char(2),@i)
select @s = 'select * into '+@table+' from dbo.student_info where 1<>1'
exec(@s)
select @s = 'insert into '+@table+' exec [dbo].[按单统计] '+@单据+','+@明细+''
exec(@s)
select @i = @i+1
end
create table #temp销售出库单 (单据 varchar(2000),明细 varchar(2000),单据类别 varchar(2000))
insert #temp销售出库单
EXEC [dbo].[按单统计]
@单据 = N'销售出库单',
@明细 = N'销售出库单明细',
@单据类别 = N'Y'
GO