Sql查询问题存储过程plz帮助 [英] Sql Query Problem Store Procedure plz help
问题描述
我有12个月的价值
Jan = 2000
2月= 2000
3月= 2月
4月= 3000
May = 2000
六月= 2000
...
创建程序[dbo]。[Proc_SaveEmployeeDeductionDetails]
@p_EmployeeId INT = 0,
@p_CalendarYearId INT = 0,
@p_CalendarMonthId INT = 0,
@p_HouseRentAmountJan decimal(16,2)= NULL,
@p_HouseRentAmountFeb decimal(16,2)= NULL,
@p_HouseRentAmountMar decimal(16,2)= NULL,
@p_HouseRentAmountApr decimal(16,2)= NULL,
@p_HouseRentAmountMay decimal(16, 2)= NULL,
@p_HouseRentAmountJun decimal(16,2)= NULL
as
开始
< br $>
如果(@ p_Action ='保存')
开始
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,年份,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,已禁用)
值 - @ p_CalendarMonthId
(@ p_EmployeeId,1,@ p_CalendarYearId,
(从PYRLL_SYSTM_CalendarYear_Master中选择姓名
,其中PK_CalendarYear_Master_Id = @ p_CalendarYearId和IsActive = 1),
@ p_HouseRentAmountJan,@ p_CreatedBy,
GETDATE() ,GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year ,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,已禁用)
值 - @ p_CalendarMonthId
(@ p_EmployeeId,2,@ p_CalendarYearId,
(从PYRLL_SYSTM_CalendarYear_Master中选择姓名
,其中PK_CalendarYear_Master_Id = @ p_CalendarYearId和IsActive = 1),
@ p_HouseRentAmountFeb,@ p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,年份,
值 - @ p_CalendarMonthId
(@ p_EmployeeId,3,@ p_CalendarYearId,
(从PYRLL_SYSTM_CalendarYear_Master中选择姓名
,其中PK_CalendarYear_Master_Id = @ p_CalendarYearId和IsActive = 1),
@ p_HouseRentAmountMar,@ p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,年份,
值 - @ p_CalendarMonthId >
(@ p_EmployeeId,4,@ p_CalendarYearId,
(从PYRLL_SYSTM_CalendarYear_Master中选择姓名
,其中PK_CalendarYear_Master_Id = @ p_CalendarYearId和IsActive = 1),
@p_HouseRentAmo untApr,@ p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,年份,
值 - @ p_CalendarMonthId
(@ p_EmployeeId,5,@ p_CalendarYearId,
(从PYRLL_SYSTM_CalendarYear_Master中选择姓名
其中PK_CalendarYear_Master_Id = @ p_CalendarYearId和IsActive = 1),
@ p_HouseRentAmountMay,@ p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
结束
结束
只有@p_HouseRentAmountJun
此值更改
如何使用循环处理此案例
@p_HouseRentAmountjan
@p_HouseRentAmountfeb
@p_HouseRentAmountmar
@p_HouseRentAmountapr
@p_HouseRentAmountMay
@p_HouseRentAmountJun
@p_HouseRentAmountJul
...
...
...
..
..
因为插入12次使用
i am handle dis case one insert statement
你在寻找这样的东西:
- TODO:将其更改为正确的类型:
DECLARE @ YearName varchar ( 50 );
SELECT
@ YearName =姓名
< span class =code-keyword> FROM
PYRLL_SYSTM_CalendarYear_Master
WHERE
PK_CalendarYear_Master_Id = @ p_CalendarYearId
和
IsActive = 1
;
WITH cteValues(MonthId,Amount) As
(
SELECT 1 , @ p_HouseRentAmountJan
UNION ALL SELECT < span class =code-digit> 2 , @ p_HouseRentAmountFeb
UNION ALL SELECT 3 , @ p_HouseRentAmountMar
UNION ALL SELECT 4 , @ p_HouseRentAmountApr
UNION ALL SELECT 5 , @ p_HouseRentAmountMay
UNION ALL SELECT 6 , @ p_HouseRentAmountJun
UNION 所有 SELECT 7 , @ p_HouseRentAmountJul
UNION ALL SELECT 8 , @ p_HouseRentAmountAug
UNION ALL SELECT 9 , @ p_HouseRentAmountSep
UNION ALL SELECT 10 , @ p_HouseRentAmountOct
UNION ALL SELECT 11 , @ p_HouseRentAmountNov
UNION ALL SELECT 12 , @ p_HouseRentAmountDec
)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(
FK_Employee_Id,
FK_Calendar_Month_Id,
FK_CalendarYear_Master_Id,
年份,
HouseRentAmount,
CreatedBy,
CreatedOn,
CreatedOnUTC,
已禁用
)
SELECT
@ p_EmployeeId ,
MonthId,
@ p_CalendarYearId ,
@YearName ,
金额,
@ p_CreatedBy ,
GetDate(),
GetUtcDate( ),
0
FROM
cteValues
;
I have 12 month value
Jan=2000
Feb=2000
Mar=2000
April=3000
May=2000
June=2000
...
create Procedure [dbo].[Proc_SaveEmployeeDeductionDetails]
@p_EmployeeId INT =0,
@p_CalendarYearId INT =0,
@p_CalendarMonthId INT =0,
@p_HouseRentAmountJan decimal(16,2)=NULL,
@p_HouseRentAmountFeb decimal(16,2)=NULL,
@p_HouseRentAmountMar decimal(16,2)=NULL,
@p_HouseRentAmountApr decimal(16,2)=NULL,
@p_HouseRentAmountMay decimal(16,2)=NULL,
@p_HouseRentAmountJun decimal(16,2)=NULL
as
Begin
If(@p_Action='Save')
Begin
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,Disabled)
values --@p_CalendarMonthId
(@p_EmployeeId,1,@p_CalendarYearId,
(select Name from PYRLL_SYSTM_CalendarYear_Master
where PK_CalendarYear_Master_Id=@p_CalendarYearId and IsActive=1),
@p_HouseRentAmountJan,@p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,Disabled)
values --@p_CalendarMonthId
(@p_EmployeeId,2,@p_CalendarYearId,
(select Name from PYRLL_SYSTM_CalendarYear_Master
where PK_CalendarYear_Master_Id=@p_CalendarYearId and IsActive=1),
@p_HouseRentAmountFeb,@p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,Disabled)
values --@p_CalendarMonthId
(@p_EmployeeId,3,@p_CalendarYearId,
(select Name from PYRLL_SYSTM_CalendarYear_Master
where PK_CalendarYear_Master_Id=@p_CalendarYearId and IsActive=1),
@p_HouseRentAmountMar,@p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,Disabled)
values --@p_CalendarMonthId
(@p_EmployeeId,4,@p_CalendarYearId,
(select Name from PYRLL_SYSTM_CalendarYear_Master
where PK_CalendarYear_Master_Id=@p_CalendarYearId and IsActive=1),
@p_HouseRentAmountApr,@p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
INSERT INTO PYRLL_TDS_Employee_HouseRentDetails
(FK_Employee_Id,FK_Calendar_Month_Id,FK_CalendarYear_Master_Id,Year,
HouseRentAmount,CreatedBy,CreatedOn,CreatedOnUTC,Disabled)
values --@p_CalendarMonthId
(@p_EmployeeId,5,@p_CalendarYearId,
(select Name from PYRLL_SYSTM_CalendarYear_Master
where PK_CalendarYear_Master_Id=@p_CalendarYearId and IsActive=1),
@p_HouseRentAmountMay,@p_CreatedBy,
GETDATE(),GETUTCDATE(),0)
End
End
only @p_HouseRentAmountJun
this value change
how to handle this case using Loop
@p_HouseRentAmountjan
@p_HouseRentAmountfeb
@p_HouseRentAmountmar
@p_HouseRentAmountapr
@p_HouseRentAmountMay
@p_HouseRentAmountJun
@p_HouseRentAmountJul
...
...
...
..
..
because insert 12 time uses
i am handle dis case one insert statement
Are you looking for something like this:
-- TODO: Change this to the correct type: DECLARE @YearName varchar(50); SELECT @YearName = Name FROM PYRLL_SYSTM_CalendarYear_Master WHERE PK_CalendarYear_Master_Id = @p_CalendarYearId And IsActive = 1 ; WITH cteValues (MonthId, Amount) As ( SELECT 1, @p_HouseRentAmountJan UNION ALL SELECT 2, @p_HouseRentAmountFeb UNION ALL SELECT 3, @p_HouseRentAmountMar UNION ALL SELECT 4, @p_HouseRentAmountApr UNION ALL SELECT 5, @p_HouseRentAmountMay UNION ALL SELECT 6, @p_HouseRentAmountJun UNION ALL SELECT 7, @p_HouseRentAmountJul UNION ALL SELECT 8, @p_HouseRentAmountAug UNION ALL SELECT 9, @p_HouseRentAmountSep UNION ALL SELECT 10, @p_HouseRentAmountOct UNION ALL SELECT 11, @p_HouseRentAmountNov UNION ALL SELECT 12, @p_HouseRentAmountDec ) INSERT INTO PYRLL_TDS_Employee_HouseRentDetails ( FK_Employee_Id, FK_Calendar_Month_Id, FK_CalendarYear_Master_Id, Year, HouseRentAmount, CreatedBy, CreatedOn, CreatedOnUTC, Disabled ) SELECT @p_EmployeeId, MonthId, @p_CalendarYearId, @YearName, Amount, @p_CreatedBy, GetDate(), GetUtcDate(), 0 FROM cteValues ;
这篇关于Sql查询问题存储过程plz帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!