42 lines
1.3 KiB
Transact-SQL
42 lines
1.3 KiB
Transact-SQL
IF OBJECT_ID('dbo.[RP_NianDuXiaoShouYuSuan]', 'P') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE dbo.RP_NianDuXiaoShouYuSuan;
|
|
PRINT '存储过程 dbo.[RP_NianDuXiaoShouYuSuan] 已删除';
|
|
END;
|
|
GO
|
|
|
|
-- 创建新的存储过程
|
|
CREATE PROCEDURE dbo.RP_NianDuXiaoShouYuSuan
|
|
@year INT = 2025
|
|
AS
|
|
BEGIN
|
|
DECLARE @month INT = 1, @sql2 NVARCHAR(MAX);
|
|
DELETE dbo.NianDuXiaoShouYuSuan
|
|
WHERE Year = @year;
|
|
|
|
--select t1.* from VHUB_t_Cust_Entry100007 t1
|
|
-- INNER JOIN (select * from VHUB_t_Cust100008 tt3 where tt3.FYEARS = @year) t2 ON t1.FID = t2.FID
|
|
|
|
PRINT @sql2;
|
|
WHILE (@month < 13)
|
|
BEGIN
|
|
SELECT @sql2
|
|
= N'INSERT INTO [dbo].[NianDuXiaoShouYuSuan]
|
|
([Fid]
|
|
,[Year]
|
|
,[Month]
|
|
,[FPrice]
|
|
,[FCUSTOMERID]
|
|
,[FBMMATERIALGROUP],MonthValue) (select t8.FID,' + CONVERT(NVARCHAR(20), @year) + N' AS [Year],'
|
|
+ CONVERT(NVARCHAR(20), @month) + N' AS [Month],FPRICE,
|
|
FCUSTOMERID,FBMMATERIALGROUP, FMONTH' + CONVERT(NVARCHAR(10), @month)
|
|
+ N' FROM VHUB_t_Cust100008 T8 LEFT JOIN VHUB_t_Cust_Entry100007 T7 ON t8.FID = t7.FID where T8.FYEARS ='
|
|
+ CONVERT(NVARCHAR(20), @year) + N')';
|
|
PRINT @sql2;
|
|
EXEC sp_executesql @sql2;
|
|
SELECT @month = @month + 1;
|
|
END;
|
|
|
|
|
|
|
|
END; |