Sql查询问题存储过程plz帮助 [英] Sql Query Problem Store Procedure plz help

查看:96
本文介绍了Sql查询问题存储过程plz帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆