Sql Query Problem Store Procedure plz help

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

Posted 17-Dec-15 1:03am

ssss0001

Add a Solution

Comments

Richard MacCutchan 17-Dec-15 7:40am

Please edit your question, add <pre> tags around your code, and explain exactly what the problem is.

ZurdoDev 17-Dec-15 8:03am

It is not clear what your question is.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-12-17T08:45:00

Are you looking for something like this:

SQL

-- 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
;