最长的存储过程

最新推荐文章于 2020-12-09 09:59:28 发布

kc675904085

最新推荐文章于 2020-12-09 09:59:28 发布

阅读量1k

点赞数 1

分类专栏： SQL 文章标签：存储 table join insert object sql

本文链接：https://blog.csdn.net/kc675904085/article/details/7484396

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

--好吧，我承认这是我学SQL两年以来写过的最长的SQL存储过程了，没有别的意思，只是发布上来纪念一下~

alter  PROCEDURE [dbo].[Sp_投资公司统一收据] (
   @StaffID numeric (18, 0), @SqlWhere nvarchar (500)
)
  -- WITH EXECUTE AS CALLER
AS
IF NOT EXISTS
(SELECT *
 FROM
	 sysobjects
 WHERE
	 id = object_id(N'[dbo].[tmp_投资公司统一收据]')
	 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[tmp_投资公司统一收据]
    (
		[年]				varchar(11),
		[月]				varchar(11),
		[日]				varchar(11),
		[百万]				varchar(11),
		[拾万]				varchar(11),
		[万]				varchar(11),
		[仟]				varchar(11),
		[佰]				varchar(11),
		[拾]				varchar(11),
		[元]				varchar(11),
		[角]				varchar(11),
		[分]				varchar(11),
		[UnitID]            numeric(18,0),
		[ProjectID]         numeric(18,0),
		[UnitFullName]      nvarchar (250) ,
		[CustName]          nvarchar (250) ,
		[ChargeDate]        datetime ,
		[ChargeCode]        nvarchar (50) ,
		[ProjectName]       nvarchar(50) ,
		[现金]				money ,
		[刷卡]				money,
		[Degree]			money,
		[NaturalSum]        money,
		[Remark]            nvarchar(250),
		[ChargeStaffName]   nvarchar(50),
		[UnitPrice]         money,
		[IsManageFee]		bit,
		[StaffID]           numeric(18,0),
		[StaffName]         varchar(50),
		[beginDate]			varchar(50),
		[endDate]			varchar(50) ,
		[projectEnName]		nvarchar(50),
		[ChargeType]		nvarchar(50),
		[总金额]			money,
		[大写总金额]		nvarchar(100),
		ChargeID			decimal,
		Account				nvarchar(50),
		[总面积]			money,
		BuildArea			money
       )
       end
else 
begin

DELETE tmp_投资公司统一收据
--插入实收应收款
EXEC ('INSERT INTO
	tmp_投资公司统一收据
SELECT 
	 cast(0 as NVARCHAR(11)) AS [年]
	,cast(0 as NVARCHAR(11)) AS [月]
	,cast(0 as NVARCHAR(11)) AS [日]
	 , cast(0 as money) AS [百万]
	 , cast(0 as money) as [拾万]
	 , cast(0 as money) as [万]
	 , cast(0 as money) as [仟]
	 , cast(0 as money) as [佰]
	 , cast(0 as money) as [拾]
	 , cast(0 as money) as [元]
	 , cast(0 as money) as [角]
	 , cast(0 as money) as [分]
	 ,TbRmUnit.UnitID
	 , TbFmAR.ProjectID
	 , TbRmUnit.UnitFullName
	 , (CASE isnull(len(TbFmCharge.Header), 0)
			WHEN 0 THEN
				TbRmCust.CustName
			ELSE
				TbFmCharge.Header
		END) AS CustName
	 , TbFmCharge.ChargeDate
	 , TbFmCharge.ChargeCode
	 , TbFmProject.ProjectName
	 , cast(0 AS MONEY) AS 现金
	 , cast(0 AS MONEY) AS 刷卡
	 , TbFmAr.Degree AS Degree
	 , TbFmChargeDetail.NaturalSum AS NaturalSum
	 , TbFmCharge.Remark
	 , TbFmCharge.ChargeStaffName
	 , TbFmAR.UnitPrice
	 , TbFmProject.IsManageFee
	 , '+@StaffID+'
	 , (SELECT staffName from tbsmstaff where staffID='+@StaffID+' )
	 , TbFmAr.BeginDate
	 , TbFmAr.EndDate
	 , TbFmProject.ProjectEnName
	 , tbPcDict.DictName AS ChargeType
	 , cast(0 AS MONEY) AS 总金额
	 , cast(0 AS NVARCHAR(100)) AS 大写总金额
	 , tbfmCharge.ChargeID
	 , TbFmCustBankAccount.Account
	 , cast(0 as decimal) as [总面积]
	 , tbrmunit.[BuildArea] as [BuildArea]
FROM
	TbFmCharge
	INNER JOIN TbFmChargeDetail
		ON TbFmCharge.ChargeID = TbFmChargeDetail.ChargeID
	INNER JOIN TbFmAR
		ON TbfmChargeDetail.ARID = TbFmAr.ARID
	LEFT JOIN TbRmUnit
		ON TbFmAR.UnitID = TbRmUnit.UnitID
	LEFT JOIN TbRmCust
		ON TbRmCust.CustID = TbFmCharge.CustID
	LEFT JOIN tbPcDict
		ON tbpcDict.dictCode = TbFmChargeDetail.ChargeType AND tbPcDict.dictTypeCode = ''FmChargeType''
left join tbSmOrgan on tbSmOrgan.OrganID = tbFmcharge.OrganID
left join TbFmCustBankAccount on TbFmCustBankAccount.CustID= tbfmCharge.CustID 
inner join TbFmProject on TbFmAR.ProjectID=TbFmProject.ProjectID 
'
+ @SqlWhere);
end

IF NOT EXISTS
(SELECT *
 FROM
	 sysobjects
 WHERE
	 id = object_id(N'[dbo].[TempCharge]')
	 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
create table TempCharge
(
	chargeID decimal,
	[现金]	  money ,
	[刷卡]	  money ,
)
end
else 
begin
delete TempCharge
end
exec ('insert INTO TempCharge
SELECT TbFmCharge.ChargeID ,sum (NaturalSum) as 现金 ,cast(0 AS money) as 刷卡 from TbFmCharge 
inner JOIN TbFmChargeDetail ON TbFmCharge.ChargeID=TbFmChargeDetail.ChargeID 
inner JOIN tbPcDict
ON tbpcDict.dictCode = TbFmChargeDetail.ChargeType AND tbPcDict.dictTypeCode = ''FmChargeType'' and DictName=''现金''
'+@SqlWhere+'
group BY TbFmCharge.chargeID,DictName')

exec ('Update  TempCharge SET TempCharge.[刷卡]=table16.刷卡
from 
(
SELECT TbFmCharge.ChargeID ,sum (NaturalSum) as 刷卡  from TbFmCharge 
inner JOIN TbFmChargeDetail ON TbFmCharge.ChargeID=TbFmChargeDetail.ChargeID 
inner JOIN tbPcDict
ON tbpcDict.dictCode = TbFmChargeDetail.ChargeType AND tbPcDict.dictTypeCode = ''FmChargeType'' AND DictName=''刷卡''
'+@SqlWhere+'
group BY TbFmCharge.chargeID,DictName) as table16
where TempCharge.ChargeID=table16.ChargeID')






--插入预收款项
--创建临时的预收款表
IF Not EXISTS 
(SELECT * FROM sysobjects
 WHERE id = object_id(N'[dbo].[Temp_预收款]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin
	exec(' CREATE TABLE [dbo].[Temp_预收款]
		(
		[年]				varchar(11),
		[月]				varchar(11),
		[日]				varchar(11),
		[百万]				varchar(11),
		[拾万]				varchar(11),
		[万]				varchar(11),
		[仟]				varchar(11),
		[佰]				varchar(11),
		[拾]				varchar(11),
		[元]				varchar(11),
		[角]				varchar(11),
		[分]				varchar(11),
		[UnitID]            numeric(18,0),
		[ProjectID]         numeric(18,0),
		[UnitFullName]      nvarchar (250) ,
		[CustName]          nvarchar (250) ,
		[ChargeDate]        datetime ,
		[ChargeCode]        nvarchar (50) ,
		[ProjectName]       nvarchar(50) ,
		[现金]				money ,
		[刷卡]				nvarchar(50),
		[Degree]			money,
		[NaturalSum]        money,
		[Remark]            nvarchar(250),
		[ChargeStaffName]   nvarchar(50),
		[UnitPrice]         money,
		[IsManageFee]		bit,
		[StaffID]           numeric(18,0),
		[StaffName]         varchar(50),
		[beginDate]			varchar(50),
		[endDate]			varchar(50) ,
		[projectEnName]		nvarchar(50),
		[ChargeType]		nvarchar(50),
		[总金额]			money,
		[大写总金额]		nvarchar(100),
		ChargeID			decimal,
		Account				nvarchar(50),
		[总面积]			money,
		[BuildArea]			money
		 )')
		 end
		 else 
		 begin
			DELETE Temp_预收款 
		 end
		 Print 12
	--获取预缴费项中的预缴开始时间以及结束时间	 
		Exec('INSERT INTO [Temp_预收款]([beginDate],[endDate],[ProjectID] ,[ChargeCode],[UnitID],[ChargeID],[ChargeDate],[ProjectName],[projectEnName])
SELECT min( StartARDate) as [StartARDate] , max (EndARDate) as [EndARDate], tbfmproject.ProjectID ,[ChargeCode],TbFmPreCharge.UnitID,TbFmCharge.ChargeID,TbFmCharge.ChargeDate,ProjectName,[projectEnName]
FROM TbFmPreCharge  inner join TbFmCharge on TbFmPreCharge.ChargeID=TbFmCharge.ChargeID
LEFT JOIN TbFmProject on TbFmPreCharge.ProjectID=TbFmProject.ProjectID 
'+@SqlWhere+'
GROUP BY tbfmproject.ProjectID,ChargeCode,TbFmCharge.ChargeID ,TbFmCharge.ChargeDate,ProjectName,[projectEnName],TbFmPreCharge.UnitID

') 
Print 13
--统计相同费项
exec ('UPDATE [Temp_预收款] SET [Temp_预收款].[NaturalSum]=table1.费项费用,
[Temp_预收款].[ChargeCode]=table1 .ChargeCode,
[Temp_预收款].[ChargeStaffName]=table1.[ChargeStaffName],[Temp_预收款].[IsManageFee]=table1.IsManageFee 
from(
SELECT TbFmPreCharge.ProjectID ,[ChargeCode] ,[ChargeStaffName] , TbFmPreCharge.UnitID ,tbFmPreCharge.ChargeID,TbFmProject.IsManageFee,ProjectName ,sum( NaturalSum) as 费项费用,
sum( NaturalSum) as NaturalSum
 FROM tbFMcharge
inner JOIN TbFmPreCharge
on  TbFmCharge.ChargeID =TbFmPreCharge.ChargeID
LEFT JOIN TbFmProject on TbFmPreCharge.ProjectID=TbFmProject.ProjectID
'+@SqlWhere+'
 GROUP BY TbFmPreCharge.ProjectID ,[ChargeCode] ,[ChargeStaffName] ,tbFmPreCharge.ChargeID,TbFmProject.IsManageFee,ProjectName ,TbFmPreCharge.UnitID 

)as table1
WHERE table1.ProjectID=[Temp_预收款].ProjectID
')
Print 14
--更新付款方式
exec('UPDATE  [Temp_预收款] SET [Temp_预收款].chargetype =table7.DictName
from
(
	SELECT DictCode ,DictName FROM TbPcDict where  tbPcDict.dictTypeCode = ''FmChargeType''
)as table7
WHERE table7.DictCode=[Temp_预收款].chargetype
')
--更新预收款现金金额
exec('UPDATE [Temp_预收款] SET [现金] =table14.现金
from(
SELECT TbPcDict.DictName ,TbFmPreCharge.ChargeID,sum ( NaturalSum ) as 现金
FROM TbFmCharge inner JOIN TbFmPreCharge  on TbFmPreCharge.ChargeID=TbFmCharge.ChargeID
INNER  JOIN tbpcdict
ON tbpcDict.dictCode = TbFmPreCharge.ChargeType AND tbPcDict.dictTypeCode = ''FmChargeType'' AND TbPcDict.DictName=''现金''
'+@SqlWhere+'
GROUP BY TbPcDict.DictName ,TbFmPreCharge.ChargeID
)as table14')

--更新预收款刷卡金额
exec('UPDATE [Temp_预收款] SET [现金] =table14.现金
from(
SELECT TbPcDict.DictName ,TbFmPreCharge.ChargeID,sum ( NaturalSum ) as 现金
FROM TbFmCharge inner JOIN TbFmPreCharge  on TbFmPreCharge.ChargeID=TbFmCharge.ChargeID
INNER  JOIN tbpcdict
ON tbpcDict.dictCode = TbFmPreCharge.ChargeType AND tbPcDict.dictTypeCode = ''FmChargeType'' AND TbPcDict.DictName=''刷卡''
'+@SqlWhere+'
GROUP BY TbPcDict.DictName ,TbFmPreCharge.ChargeID
)as table14')

Print 15
 --单元信息,租户信息
 exec ('UPDATE [Temp_预收款] SET [Temp_预收款].UnitFullName=table2.UnitFullName,
[Temp_预收款].BuildArea=table2.BuildArea,[Temp_预收款].CustName=table2.CustName,[Temp_预收款].UnitID=table2.UnitID 
FROM 
(
 SELECT UnitFullName,BuildArea, TbRmCust.CustName ,TbFmCharge.ChargeID ,TbFmPreCharge .UnitID 
 FROM  TbFmCharge inner JOIN TbFmPreCharge  on TbFmPreCharge.ChargeID=TbFmCharge.ChargeID 
 LEFT JOIN TbRmUnit on TbFmPreCharge.UnitID =TbRmUnit.UnitID 
 LEFT JOIN TbRmCust on TbRmCust .CustID=TbFmCharge.CustID 
 '+@SqlWhere+'
 ) as table2
 WHERE [Temp_预收款].UnitID=table2.UnitID')
Print 16
 --将预收临时表数据插入tmp_投资公司统一收据 select * from tmp_投资公司统一收据
 exec(' INSERT INTO tmp_投资公司统一收据
 SELECT * FROM [Temp_预收款]')
Print 17
 --删除预收临时表
--exec ('DROP TABLE [Temp_预收款]')

--EXEC ('update tmp_投资公司统一收据 set tmp_投资公司统一收据.Degree=unit.BuildArea
--from TbRmUnit unit
--where tmp_投资公司统一收据.UnitID=unit.UnitID
--and tmp_投资公司统一收据.StaffID='
--+ @StaffID + '
--and tmp_投资公司统一收据.IsManageFee=1'
--)
Print 18
--创建新的数据源表
IF NOT EXISTS (SELECT * FROM  sysobjects WHERE id = object_id(N'[dbo].[tmp_投资公司统一收据_标准]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 begin
	exec(' CREATE TABLE [dbo].[tmp_投资公司统一收据_标准]
    (
		[年]				varchar(11),
		[月]				varchar(11),
		[日]				varchar(11),
		[百万]				varchar(11),
		[拾万]				varchar(11),
		[万]				varchar(11),
		[仟]				varchar(11),
		[佰]				varchar(11),
		[拾]				varchar(11),
		[元]				varchar(11),
		[角]				varchar(11),
		[分]				varchar(11),
		[UnitID]            numeric(18,0),
		[ProjectID]         numeric(18,0),
		[UnitFullName]      nvarchar (250) ,
		[CustName]          nvarchar (250) ,
		[ChargeDate]        datetime ,
		[ChargeCode]        nvarchar (50) ,
		[ProjectName]       nvarchar(50) ,
		[现金]				money ,
		[刷卡]				money,
		[NaturalSum]        money,
		[ChargeStaffName]   nvarchar(50),
		[StaffID]           numeric(18,0),
		[StaffName]         varchar(50),
		[beginDate]			varchar(50),
		[endDate]			varchar(50) ,
		[projectEnName]		nvarchar(50),
		[ChargeType]		nvarchar(50),
		[总金额]			money,
		[ChargeID]	decimal,
		[总面积]			money,
		[BuildArea]			money,
		[费项费用]			money
       )
')
 end
else 
begin
	DELETE [tmp_投资公司统一收据_标准]
end 


Print 19

--插入单元信息
exec('
INSERT INTO [tmp_投资公司统一收据_标准](BuildArea,ChargeID,staffID,staffName,ProjectID)
SELECT sum ( BuildArea) AS BuildArea ,ChargeID,staffID,staffName,ProjectID FROM [tmp_投资公司统一收据]
GROUP BY ChargeID,staffID,staffName,ProjectID
')
--修改计费开始时间以及结束时间
Print 20
exec('UPDATE [tmp_投资公司统一收据_标准] 
SET [tmp_投资公司统一收据_标准].[beginDate]=CONVERT(varchar(100), table1.beginDate, 23),
	[tmp_投资公司统一收据_标准].[endDate]=CONVERT(varchar(100),table1.endDate, 23)
from 
(
	SELECT min([beginDate]) as [beginDate] ,max ([endDate]) as [endDate] ,ChargeID,ProjectID FROM [tmp_投资公司统一收据]
	GROUP BY ChargeID,ProjectID
) as table1
WHERE [tmp_投资公司统一收据_标准].ProjectID=table1.ProjectID ')

Print 21
--修改用户信息
exec ('UPDATE [tmp_投资公司统一收据_标准] SET [tmp_投资公司统一收据_标准].CustName=table2.CustName
FROM 
(
	SELECT DISTINCT ChargeID,CustName FROM [tmp_投资公司统一收据]
)as table2
WHERE [tmp_投资公司统一收据_标准].ChargeID=table2.ChargeID ')

--相关费项信息

Print 22
exec ('
UPDATE [tmp_投资公司统一收据_标准] 
SET
	  [tmp_投资公司统一收据_标准].ProjectID=table3.ProjectID,
	  [tmp_投资公司统一收据_标准].ChargeDate=table3.ChargeDate,
	  [tmp_投资公司统一收据_标准].ChargeCode=table3.ChargeCode,
	  [tmp_投资公司统一收据_标准].ProjectName=table3.ProjectName,
      [tmp_投资公司统一收据_标准].projectEnName=table3.projectEnName,
	  [tmp_投资公司统一收据_标准].ChargeStaffName=table3.ChargeStaffName,
	  [tmp_投资公司统一收据_标准].现金=table3.现金,
	  [tmp_投资公司统一收据_标准].刷卡=table3.刷卡,
	  [tmp_投资公司统一收据_标准].费项费用=table3.费项费用,
	  [tmp_投资公司统一收据_标准].NaturalSum=table3.NaturalSum
	  FROM 
(
	SELECT   ProjectID ,ChargeDate ,ChargeCode ,ProjectName , projectEnName  , ChargeID ,ChargeStaffName
	,sum (现金) as 现金 ,sum (刷卡) as 刷卡 ,sum( NaturalSum) as 费项费用 ,sum( NaturalSum) as NaturalSum FROM [tmp_投资公司统一收据]
	GROUP BY  ProjectID ,ChargeDate ,ChargeCode ,ProjectName ,ChargeID, projectEnName ,ChargeStaffName
)as table3
WHERE [tmp_投资公司统一收据_标准].ProjectID=table3.ProjectID ')
Print 23
--更新总金额
EXEC ('update  [tmp_投资公司统一收据_标准]  set [总金额]=table1.NaturalSum
 from 
(select sum(NaturalSum) as NaturalSum ,ChargeCode from [tmp_投资公司统一收据_标准]  GROUP BY ChargeCode) as table1
WHERE table1.ChargeCode=[tmp_投资公司统一收据_标准] .ChargeCode')
Print 24
--更新前面的大写数据列
Exec ('UPDATE [tmp_投资公司统一收据_标准] 
SET
	[百万] = dbo.slbig([总金额], 9),
	[拾万] = dbo.slbig([总金额], 8),
	[万] = dbo.slbig([总金额], 7),
	[仟] = dbo.slbig([总金额], 6),
	[佰] = dbo.slbig([总金额], 5),
	[拾] = dbo.slbig([总金额], 4),
	[元] = dbo.slbig([总金额], 3),
	[角] = dbo.slbig([总金额], 2),
	[分] = dbo.slbig([总金额], 1)')

--更新前面的时间列
Print 25
Exec ('UPDATE [tmp_投资公司统一收据_标准] 
SET 
	[年] =YEAR (ChargeDate)')

Exec ('UPDATE [tmp_投资公司统一收据_标准] 
SET 
	[月] =MONTH(ChargeDate)')
	
Exec ('UPDATE [tmp_投资公司统一收据_标准] 
SET 
	[日] =DAY (ChargeDate)')	

--更新刷卡金额
Print 26
EXEC ('update [tmp_投资公司统一收据_标准]  set [刷卡]=table3.NaturalSum
from
(select 刷卡 as NaturalSum,ChargeCode from [Temp_预收款] ) as table3
WHERE table3.ChargeCode=[tmp_投资公司统一收据_标准] .ChargeCode')

EXEC ('update [tmp_投资公司统一收据_标准]  set [刷卡]=[刷卡]+table23.NaturalSum
from 
(select 刷卡 as NaturalSum ,ChargeID from TempCharge ) as table23
WHERE table23.ChargeID=[tmp_投资公司统一收据_标准] .ChargeID
')


--更新现金
EXEC ('update [tmp_投资公司统一收据_标准]  set [现金]=table4.NaturalSum
from 
(select 现金 as NaturalSum ,ChargeID from [Temp_预收款] ) as table4
WHERE table4.ChargeID=[tmp_投资公司统一收据_标准] .ChargeID
')

EXEC ('update [tmp_投资公司统一收据_标准]  set [现金]=[现金]+table24.NaturalSum
from 
(select 现金 as NaturalSum ,ChargeID from TempCharge) as table24
WHERE table24.ChargeID=[tmp_投资公司统一收据_标准] .ChargeID
')




Print 28
--更新总面积
Exec('Update [tmp_投资公司统一收据_标准]  SET [总面积]=table2.SumArea
FROM (SELECT sum (BuildArea) as SumArea ,ChargeCode FROM [tmp_投资公司统一收据]  GROUP BY ChargeCode) as table2
WHERE table2.ChargeCode=[tmp_投资公司统一收据_标准] .ChargeCode')

Print 29
--添加单元全名字段
Exec('if  not exists (select *  from   syscolumns   where   id=object_id(''[tmp_投资公司统一收据_标准]'')   and   name=''UnitAllName'')   
alter table [tmp_投资公司统一收据_标准] add UnitAllName varchar(500)')
Print 30
--修改值
Exec('DECLARE @unitfullname varchar(500)
set @unitfullname = ''　''
select Top 2 @unitfullname=@unitfullname+unitfullname+'','' from [tmp_投资公司统一收据]
GROUP BY UnitFullName 
Update [tmp_投资公司统一收据_标准]
SET  UnitAllName =left(@unitfullname,len(isnull(@unitfullname,''''))-1)')

exec
(
	'Update  [tmp_投资公司统一收据_标准] SET [tmp_投资公司统一收据_标准].StaffID=table11.StaffID, [tmp_投资公司统一收据_标准].StaffName =table11.StaffName
	from ( SELECT StaffName,StaffID from tbsmstaff WHERE StaffID='+@StaffID +') as table11'
)

go

kc675904085

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
最长的存储过程

--好吧，我承认这是我学SQL两年以来写过的最长的SQL存储过程了，没有别的意思，只是发布上来纪念一下~alter PROCEDURE [dbo].[Sp_投资公司统一收据] ( @StaffID numeric (18, 0), @SqlWhere nvarchar (500)) -- WITH EXECUTE AS CALLERASIF NOT EXISTS(SELECT
复制链接

扫一扫

专栏目录