error in Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

error in Procedure

Author

Topic

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-11 : 07:57:39

with an event a procedure is being called up

procedure is working properly with other conditions and other ids but wen i select a particular series then error comes like

Server: Msg 105, Level 15, State 1, Line 18
Unclosed quotation mark before the character string '908017'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near '908017'.

i m sending u the tracer output of my procedure

canu tell me is it the procedure error or something else

exec ProcActualSalary 1100, 1110, 'May 1 2007 12:00AM', 'May 31 2007 12:00AM', ' ''90801083'',''90801143'',''90801144'',''90801145'',''90801146'',''90801147'',''90801148'',''90801149'',''90801150'',''90801151'',''90801152'',''90801153'',''90801154'',''90801155'',''90801156'',''90801157'',''90801158'',''90801159'',''90801160'',''90801161'',''90801162'',''90801163'',''90801164'',''90801165'',''90801166'',''90801167'',''90801168'',''90801169'',''90801170'',''90801171'',''90801172'',''90801173'',''90801174'',''90801175'',''90801176'',''90801177'',''90801178'',''90801179'',''90801180'',''90801181'',''90801182'',''90801183'',''90801184'',''90801185'',''90801186'',''90801187'',''90801188'',''90801189'',''90801190'',''90801191'',''90801192'',''90801193'',''90801194'',''90801195'',''90801196'',''90801197'',''90801198'',''90801199'',''90801200'',''90801201'',''90801202'',''90801203'',''90801204'',''90801205'',''90801206'',''90801207'',''90801208'',''90801209'',''90801210'',''90801211'',''90801212'',''90801213'',''90801214'',''90801215'',''90801216'',''90801217'',''90801218'',''90801219'',''90801220'',''90801221'',''90801222'',''90801223'',''90801224'',''90801225'',''90801226'',''90801227'',''90801228'',''90801229'',''90801230'',''90801231'',''90801232'',''90801233'',''90801234'',''90801235'',''90801236'',''90801237'',''90801238'',''90801239'',''90801240'',''90801241'',''90801242'',''90801243'',''90801244'',''90801245'',''90801246'',''90801247'',''90801248'',''90801249'',''90801250'',''90801251'',''90801252'',''90801253'',''90801254'',''90801255'',''90801256'',''90801257'',''90801258'',''90801259'',''90801260'',''90801261'',''90801262'',''90801263'',''90801264'',''90801265'',''90801266'',''90801267'',''90801268'',''90801269'',''90801270'',''90801271'',''90801272'',''90801273'',''90801274'',''90801275'',''90801276'',''90801277'',''90801278'',''90801279'',''90801280'',''90801281'',''90801283'',''90801284'',''90801285'',''90801286'',''90801287'',''90801288'',''90801289'',''90801290'',''90801291'',''90801292'',''90801293'',''90801294'',''90801295'',''90801296'',''90801297'',''90801298'',''90801299'',''90801300'',''90801301'',''90801302'',''90801303'',''90801304'',''90801305'',''90801306'',''90801307'',''90801308'',''90801309'',''90801310'',''90801311'',''90801312'',''90801313'',''90801314'',''90801315'',''90801320'',''90801322'',''90801325'',''90801326'',''90801327'',''90801328'',''90801329'',''90801330'',''90801331'',''90801333'',''90801334'',''90801336'',''90801337'',''90801338'',''90801339'',''90801340'',''90801341'',''90801342'',''90801345'',''90801347'',''90801348'',''90801353'',''90801356'',''90801357'',''90801358'',''90801359'',''90801360'',''90801361'',''90801362'',''90801363'',''90801364'',''90801365'',''90801366'',''90801367'',''90801368'',''90801369'',''90801370'',''90801371'',''90801372'',''90801373'',''90801381'',''90801382'',''90801383'',''90801384'',''90801385'',''90801386'',''90801387'',''90801388'',''90801389'',''90801390'',''90801391'',''90801392'',''90801393'',''90801394'',''90801395'',''90801396'',''90801397'',''90801398'',''90801399'',''90801400'',''90801401'',''90801402'',''90801403'',''90801404'',''90801405'',''90801406'',''90801407'',''90801408'',''90801409'',''90801410'',''90801411'',''90801412'',''90801413'',''90801414'',''90801415'',''90801416'',''90801417'',''90801418'',''90801419'',''90801420'',''90801421'',''90801422'',''90801423'',''90801424'',''90801425'',''90801426'',''90801427'',''90801428'',''90801429'',''90801430'',''90801431'',''90801432'',''90801433'',''90801434'',''90801435'',''90801436'',''90801437'',''90801438'',''90801439'',''90801440'',''90801441'',''90801442'',''90801443'',''90801444'',''90801445'',''90801446'',''90801447'',''90801448'',''90801449'',''90801450'',''90801451'',''90801452'',''90801453'',''90801454'',''90801455'',''90801456'',''90801457'',''90801461'',''90801462'',''90801463'',''90801464'',''90801465'',''90801466'',''90801467'',''90801468'',''90801469'',''90801470'',''90801471'',''90801472'',''90801473'',''90801474'',''90801475'',''90801476'',''90801477'',''90801478'',''90801479'',''90801480'',''90801481'',''90801482'',''90801483'',''90801484'',''90801485'',''90801486'',''90801487'',''90801488'',''90801489'',''90801490'',''90801491'',''90801492'',''90801493'',''90801494'',''90801495'',''90801496'',''90801497'',''90801498'',''90801499'',''90801500'',''90801501'',''90801502'',''90801503'',''90801504'',''90801505'',''90801506'',''90801507'',''90801511'',''90801512'',''90801513'',''90801514'',''90801516'',''90801517'',''90801518'',''90801519'',''90801520'',''90801521'',''90801522'',''90801523'',''90801524'',''90801525'',''90801526'',''90801527'',''90801528'',''90801529'',''90801530'',''90801531'',''90801532'',''90801533'',''90801534'',''90801535'',''90801536'',''90801537'',''90801538'',''90801539'',''90801540'',''90801541'',''90801542'',''90801543'',''90801544'',''90801545'',''90801546'',''90801547'',''90801548'',''90801549'',''90801550'',''90801551'',''90801552'',''90801553'',''90801554'',''90801555'',''90801556'',''90801557'',''90801558'',''90801559'',''90801560'',''90801561'',''90801562'',''90801563'',''90801564'',''90801565'',''90801566'',''90801567'',''90801568'',''90801569'',''90801570'',''90801571'',''90801572'',''90801573'',''90801574'',''90801575'',''90801576'',''90801577'',''90801578'',''90801579'',''90801580'',''90801581'',''90801582'',''90801583'',''90801584'',''90801585'',''90801587'',''90801588'',''90801589'',''90801593'',''90801594'',''90801595'',''90801596'',''90801597'',''90801598'',''90801599'',''90801600'',''90801601'',''90801602'',''90801604'',''90801606'',''90801607'',''90801608'',''90801609'',''90801610'',''90801611'',''90801612'',''90801613'',''90801614'',''90801615'',''90801616'',''90801617'',''90801618'',''90801619'',''90801621'',''90801622'',''90801623'',''90801624'',''90801625'',''90801626'',''90801627'',''90801628'',''90801629'',''90801630'',''90801631'',''90801632'',''90801633'',''90801634'',''90801635'',''90801636'',''90801637'',''90801638'',''90801639'',''90801640'',''90801641'',''90801642'',''90801643'',''90801644'',''90801645'',''90801648'',''90801649'',''90801650'',''90801651'',''90801653'',''90801654'',''90801655'',''90801658'',''90801659'',''90801660'',''90801661'',''90801662'',''90801663'',''90801664'',''90801665'',''90801666'',''90801667'',''90801668'',''90801669'',''90801670'',''90801671'',''90801672'',''90801673'',''90801674'',''90801675'',''90801676'',''90801677'',''90801678'',''90801679'',''90801680'',''90801681'',''90801682'',''90801683'',''90801684'',''90801685'',''90801686'',''90801687'',''90801688'',''90801689'',''90801690'',''90801691'',''90801692'',''90801693'',''90801694'',''90801695'',''90801696'',''90801697'',''90801699'',''90801700'',''90801701'',''90801703'',''90801704'',''90801705'',''90801706'',''90801707'',''90801708'',''90801709'',''90801710'',''90801711'',''90801712'',''90801713'',''90801714'',''90801715'',''90801716'',''90801717'',''90801718'',''90801719'',''90801720'',''90801721'',''90801722'',''90801723'',''90801724'',''90801725'',''90801726'',''90801727'',''90801728'',''90801729'',''90801730'',''90801731'',''90801732'',''90801733'',''90801734'',''90801736'',''90801737'',''90801738'',''90801739'',''90801740'',''90801741'',''90801742'',''90801743'',''90801744'',''90801745'',''90801746'',''90801747'',''90801748'',''90801749'',''90801750'',''90801751'',''90801752'',''90801753'',''90801754'',''90801755'',''90801756'',''90801757'',''90801758'',''90801759'',''90801760'',''90801761'',''90801762'',''90801763'',''90801764'',''90801765'',''90801766'',''90801767'',''90801768'',''90801769'',''90801770'',''90801771'',''90801772'',''90801773'',''90801774'',''90801775'',''90801776'',''90801777'',''90801778'',''90801779'',''90801780'',''90801781'',''90801782'',''90801783'',''90801784'',''90801785'',''90801786'',''90801787'',''90801788'',''90801789'',''90801790'',''90801791'',''90801792'',''90801793'',''90801794'',''90801795'',''90801796''', '', ''

i had checked the quotation mark it seems to be correct

Please help out

which type of error it is and how i can solve

With Best Regards
Rubal

With Best Regards
Ruby

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-11 : 08:06:38

i can send the procedure details if somebody want that for help

do provide me a solid solution

With Best Regards
rubal

With Best Regards
Ruby

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 08:56:23

What happens when you use single single quote instead of double?

ex
Instead of
''90801083''

use
'90801083'

and see what happens

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 08:59:14

It seems you are passing set of values as last parameter
Try these approaches
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-11 : 09:05:39

i need a bigger monitor

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 09:24:25

quote:
Originally posted by khtan

i need a bigger monitor

KH

Also lot of blanks between values

Madhivanan

Failing to plan is Planning to fail

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-12 : 04:46:42

friends

can somebody tell me is it the error of procedure or can be anything else

With Best Regards
Ruby

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 04:58:16

the SP ProcActualSalary is expecting how many parameters ?

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-12 : 06:27:31

this is the code of the procedure

Hope now u can help out

CREATE PROCEDURE ProcActualSalary
@intCompanyCode INTEGER,
@intPlantCode INTEGER,
@DtSalFrom DATETIME,
@DtSalTo DATETIME,
@strPersonnelID1 VARCHAR(8000),
@strPersonnelID2 VARCHAR(8000),
@strPersonnelID3 VARCHAR(8000)

AS

--Variable Declaration
DECLARE @strQry VARCHAR(8000)
DECLARE @strHeadCode VARCHAR(5)
DECLARE @strHeadDesc VARCHAR(100)
DECLARE @intHeadAmt NUMERIC(18,2)

DECLARE @strMainPerId VARCHAR(20)
DECLARE @strPerId VARCHAR(20)
DECLARE @DtFromDate DATETIME
DECLARE @DtToDate DATETIME

DECLARE @strESICNo VARCHAR(25)
DECLARE @strPFNo VARCHAR(25)
DECLARE @strGrade VARCHAR(50)
DECLARE @strDesignation VARCHAR(50)
DECLARE @strDepartment VARCHAR(50)
DECLARE @strCity VARCHAR(50)

DECLARE @intPresentDays NUMERIC(5,2)
DECLARE @intAbsentDays NUMERIC(5,2)
DECLARE @intLeaveDays NUMERIC(5,2)
DECLARE @intWODays NUMERIC(5,2)
DECLARE @intWDays NUMERIC(5,2)
DECLARE @intODays NUMERIC(5,2)
DECLARE @intWPDays NUMERIC(5,2)
DECLARE @intEarnedLeaves NUMERIC(5,2)
DECLARE @intEarnedLeavesHQT NUMERIC(5,2)
DECLARE @intOtherLeaves NUMERIC(5,2)
DECLARE @intOtherLeavesHQT NUMERIC(5,2)
DECLARE @intTotalDays NUMERIC(5,2)
DECLARE @intTotalWorkingDays NUMERIC(5,2)
DECLARE @intTotalPayDays NUMERIC(5,2)
DECLARE @intTotalWorkingPayDays NUMERIC(5,2)

DECLARE @I NUMERIC(2,0)
DECLARE @intDay NUMERIC(1,0)

DECLARE @intFixedAmt NUMERIC(18,2)
DECLARE @strNotIn VARCHAR(1000) --For Date Range for which Records is updated
--End Variables Declaration

--Checking & Creating TempActualSalary Table
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempActualSalary]') and OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE TempActualSalary

CREATE TABLE TempActualSalary
(
PLANT_CODE INTEGER,
PLANT_DESC VARCHAR(50),
COMPANY_CODE INTEGER,
APPLICABLE_FROM DATETIME,
APPLICABLE_TO DATETIME,
SALMONTH INTEGER,
SALYEAR INTEGER,
PERSONNEL_ID VARCHAR(20),
ID_CARD VARCHAR(20),
PERSONNEL_FNAME VARCHAR(25),
PERSONNEL_MNAME VARCHAR(25),
PERSONNEL_LNAME VARCHAR(25),
FATHER_HUSB_NAME VARCHAR(25),
ESIC_NO VARCHAR(25),
PF_NO VARCHAR(25),
GRADE VARCHAR(50),
DESIGNATION VARCHAR(50),
DEPARTMENT VARCHAR(50),
BUILDING VARCHAR(50),
STREET1 VARCHAR(50),
STREET2 VARCHAR(50),
CITY VARCHAR(50),
STATE VARCHAR(50),
COUNTRY VARCHAR(50),
PINCODE VARCHAR(10),
PRESENT_DAYS NUMERIC(5,2), --No. of Present Days
ABSENT_DAYS NUMERIC(5,2), --No. Of Absent Days
WO_DAYS NUMERIC(5,2), --No. Of Week Off & Holiday Days
W_DAYS NUMERIC(5,2), --No. Of Week Off Days
O_DAYS NUMERIC(5,2), --No. Of Holiday Days
WP_DAYS NUMERIC(5,2), --No. Of Without Pay Days
LEAVE_DAYS NUMERIC(5,2), --No. Of Leave Days
EARNED_LEAVE NUMERIC(5,2),
OTHER_LEAVE NUMERIC(5,2),
TOTAL_PAY_DAYS NUMERIC(5,2),
TOTAL_WRK_PAY_DAYS NUMERIC(5,2),
TOTAL_DAYS NUMERIC(5,2),
TOTAL_WRK_DAYS NUMERIC(5,2),
DAY1 TINYINT,
DAY2 TINYINT,
DAY3 TINYINT,
DAY4 TINYINT,
DAY5 TINYINT,
DAY6 TINYINT,
DAY7 TINYINT,
DAY8 TINYINT,
DAY9 TINYINT,
DAY10 TINYINT,
DAY11 TINYINT,
DAY12 TINYINT,
DAY13 TINYINT,
DAY14 TINYINT,
DAY15 TINYINT,
DAY16 TINYINT,
DAY17 TINYINT,
DAY18 TINYINT,
DAY19 TINYINT,
DAY20 TINYINT,
DAY21 TINYINT,
DAY22 TINYINT,
DAY23 TINYINT,
DAY24 TINYINT,
DAY25 TINYINT,
DAY26 TINYINT,
DAY27 TINYINT,
DAY28 TINYINT,
DAY29 TINYINT,
DAY30 TINYINT,
DAY31 TINYINT
)
--Checking & Creating TempActualSalary Table
-- select * from TempActualSalary
--Code For Adding Field in TempActualSalary Table (According To Earning / Deduction Heads)
DECLARE ActualSalaryCur CURSOR FOR
SELECT DISTINCT HEAD_CODE FROM PA_SALARY_M

OPEN ActualSalaryCur

FETCH NEXT FROM ActualSalaryCur
INTO @strHeadCode

WHILE @@FETCH_STATUS = 0
BEGIN
--Adding Field in TempActualSalary Table (As per Earning / Deduction Head)
EXEC ('ALTER TABLE TempActualSalary ADD [' + @strHeadCode + '] NUMERIC(18,2) NULL')
EXEC ('ALTER TABLE TempActualSalary ADD [FIXED_' + @strHeadCode + '] NUMERIC(18,2) NULL')
--End Adding Field in TempActualSalary Table (As per Earning / Deduction Head)

FETCH NEXT FROM ActualSalaryCur
INTO @strHeadCode
END

CLOSE ActualSalaryCur
DEALLOCATE ActualSalaryCur
--End Code For Adding Field in TempActualSalary Table (According To Earning / Deduction Heads)

--Inserting Common Details
Set @strQry = 'INSERT INTO TempActualSalary (PLANT_CODE, PLANT_DESC, COMPANY_CODE, SALMONTH, SALYEAR, APPLICABLE_FROM, APPLICABLE_TO, PERSONNEL_ID, ID_CARD,
PERSONNEL_FNAME, PERSONNEL_MNAME, PERSONNEL_LNAME, FATHER_HUSB_NAME, BUILDING, STREET1, STREET2, STATE, COUNTRY, PINCODE)

SELECT DISTINCT PA_PER_COMP_SAL_T.PLANT_CODE, GL_PLANT_C.PLANT_DESC, GL_PLANT_C.COMPANY_CODE, MONTH(PA_PER_COMP_SAL_T.APPLICABLE_FROM) AS SALMONTH, YEAR(PA_PER_COMP_SAL_T.APPLICABLE_FROM) AS SALYEAR,
PA_PER_COMP_SAL_T.APPLICABLE_FROM, PA_PER_COMP_SAL_T.APPLICABLE_UPTO, PA_PER_COMP_SAL_T.PERSONNEL_ID, PA_PERSONNEL_M.ID_CARD, PA_PERSONNEL_M.PERSONNEL_FNAME, PA_PERSONNEL_M.PERSONNEL_MNAME,
PA_PERSONNEL_M.PERSONNEL_LNAME, PA_PERSONNEL_M.FATHER_HUSB_NAME, GL_PLANT_C.BUILDING, GL_PLANT_C.STREET1, GL_PLANT_C.STREET2, GL_REGION_C.REGION_NAME, GL_COUNTRY_C.DESCRIPTION AS COUNTRY,
GL_PLANT_C.PINCODE

FROM PA_PER_COMP_SAL_T LEFT JOIN PA_PERSONNEL_M ON PA_PER_COMP_SAL_T.PLANT_CODE = PA_PERSONNEL_M.PLANT_CODE AND PA_PER_COMP_SAL_T.PERSONNEL_ID = PA_PERSONNEL_M.PERSONNEL_ID

LEFT JOIN GL_PLANT_C ON PA_PER_COMP_SAL_T.PLANT_CODE = GL_PLANT_C.PLANT_CODE
LEFT JOIN GL_AREA_C ON GL_PLANT_C.CITY = GL_AREA_C.AREA_CODE
LEFT JOIN GL_REGION_C ON GL_PLANT_C.REGION_STATE = GL_REGION_C.REGION_CODE
LEFT JOIN GL_COUNTRY_C ON GL_PLANT_C.COUNTRY = GL_COUNTRY_C.COUNTRY_CODE

WHERE (PA_PER_COMP_SAL_T.APPLICABLE_FROM BETWEEN ''' + CONVERT(VARCHAR(20), @DtSalFrom, 101) + ''' AND ''' + CONVERT(VARCHAR(20), @DtSalTo, 101) + ''')
AND PA_PER_COMP_SAL_T.PLANT_CODE = ''' + CONVERT(VARCHAR(4),@intPlantCode) + ''' AND GL_PLANT_C.COMPANY_CODE = ''' + CONVERT(VARCHAR(4),@intCompanyCode) + '''
AND PA_PER_COMP_SAL_T.PERSONNEL_ID IN ('+@strPersonnelID1+@strPersonnelID2+@strPersonnelID3+')

ORDER BY PA_PER_COMP_SAL_T.APPLICABLE_FROM, PA_PER_COMP_SAL_T.APPLICABLE_UPTO, PA_PER_COMP_SAL_T.PERSONNEL_ID'

EXEC (@strQry)
--End Common details

--Updating Details
DECLARE ActualSalaryCur CURSOR FOR --For Moving Each Employee
SELECT DISTINCT PERSONNEL_ID FROM TempActualSalary

OPEN ActualSalaryCur

FETCH NEXT FROM ActualSalaryCur
INTO @strMainPerId

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ActualSalaryCur1 CURSOR FOR --For Retreiving Different Records of Main Employee
SELECT PERSONNEL_ID, APPLICABLE_FROM, APPLICABLE_TO
FROM TempActualSalary
WHERE PERSONNEL_ID = @strMainPerId

OPEN ActualSalaryCur1

FETCH NEXT FROM ActualSalaryCur1
INTO @strPerId, @DtFromDate, @DtToDate

IF @@FETCH_STATUS <> 0
BEGIN
CLOSE ActualSalaryCur1
DEALLOCATE ActualSalaryCur1
END

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ActualSalaryCur2 CURSOR FOR --For Retreiving Defferent Head Code
SELECT DISTINCT HEAD_CODE FROM PA_SALARY_M

OPEN ActualSalaryCur2

FETCH NEXT FROM ActualSalaryCur2
INTO @strHeadCode

IF @@FETCH_STATUS <> 0
BEGIN
CLOSE ActualSalaryCur1
DEALLOCATE ActualSalaryCur1

CLOSE ActualSalaryCur2
DEALLOCATE ActualSalaryCur2
END

WHILE @@FETCH_STATUS = 0
BEGIN
--Retreiving Head Amount
SELECT @intHeadAmt = (SELECT AMOUNT FROM PA_PER_COMP_SAL_T WHERE PERSONNEL_ID = @strPerId AND APPLICABLE_FROM = @DtFromDate AND APPLICABLE_UPTO = @DtToDate AND HEAD_CODE = @strHeadCode AND PLANT_CODE = @intPlantCode)
--End Retreiving Head Amount

--Updating Amount For Each Head in TempActualSalary Table
IF @intHeadAmt IS NOT NULL
BEGIN
DECLARE @strSql varchar(5000)
select @strSql ='UPDATE TempActualSalary SET [' + @strHeadCode + '] = ' + CAST(@intHeadAmt AS VARCHAR(20))
+ ' WHERE PERSONNEL_ID = ' + @strPerId + ' AND PLANT_CODE = ' + CAST(@intPlantCode AS VARCHAR(20))
+ ' AND APPLICABLE_FROM = ''' + CONVERT(VARCHAR(20), @DtFromDate, 101) + ''''
+ ' AND APPLICABLE_TO = ''' + CONVERT(VARCHAR(20), @DtToDate, 101) + ''''
EXEC (@strSql)
END
--End Updating Deduction Amount For Each Head in TempActualSalary Table

FETCH NEXT FROM ActualSalaryCur2
INTO @strHeadCode

IF @@FETCH_STATUS <> 0
BEGIN
CLOSE ActualSalaryCur2
DEALLOCATE ActualSalaryCur2
END
END
FETCH NEXT FROM ActualSalaryCur1
INTO @strPerId, @DtFromDate, @DtToDate

END
CLOSE ActualSalaryCur1
DEALLOCATE ActualSalaryCur1

FETCH NEXT FROM ActualSalaryCur
INTO @strMainPerId
END

CLOSE ActualSalaryCur
DEALLOCATE ActualSalaryCur
--End Updating Earning / Deductions Details

--Updating ESIC No., PF No., Designation, Department & City field of TempActualSalary Table
DECLARE ActualSalaryCur CURSOR FOR --For Retreiving Personnel ID in TempActualSalary
SELECT DISTINCT PERSONNEL_ID, PLANT_CODE, APPLICABLE_FROM, APPLICABLE_TO FROM TempActualSalary

OPEN ActualSalaryCur

FETCH NEXT FROM ActualSalaryCur
INTO @strPerId, @intPlantCode, @DtFromDate, @DtToDate

WHILE @@FETCH_STATUS = 0
BEGIN
--Retreiving ESIC No.
SET @strESICNo = ''

SELECT @strESICNo = STATUTORY_NO FROM PA_PER_STATUTORY_D
WHERE STATUTORY_CODE = 'ESI' AND PERSONNEL_ID = @strPerId

--Retreiving PF No.
SELECT @strPFNo = STATUTORY_NO FROM PA_PER_STATUTORY_D
WHERE STATUTORY_CODE = 'PF' AND PERSONNEL_ID = @strPerId

--Retreiving Grade
SET @strGrade = ''

SELECT @strGrade = B.DESCRIPTION FROM PA_PERSONNEL_M_ENTITY_D A LEFT JOIN PA_ENTITY_D B ON A.PLANT_CODE = B.PLANT_CODE
AND A.ENTITY_CODE = B.ENTITY_CODE AND A.ENTITY_SUB_CODE = B.ENTITY_SUB_CODE
WHERE A.PERSONNEL_ID = @strPerId AND A.PLANT_CODE = @intPlantCode AND B.ENTITY_CODE = 'GR1'

--Retreiving Designation
SET @strDesignation = ''

SELECT @strDesignation = B.DESCRIPTION FROM PA_PERSONNEL_M_ENTITY_D A LEFT JOIN PA_ENTITY_D B ON A.PLANT_CODE = B.PLANT_CODE
AND A.ENTITY_CODE = B.ENTITY_CODE AND A.ENTITY_SUB_CODE = B.ENTITY_SUB_CODE
WHERE A.PERSONNEL_ID = @strPerId AND A.PLANT_CODE = @intPlantCode AND B.ENTITY_CODE = 'DSG'

--Retreiving Department
SET @strDepartment = ''

SELECT @strDepartment = B.DESCRIPTION FROM PA_PERSONNEL_M_ENTITY_D A LEFT JOIN PA_ENTITY_D B ON A.PLANT_CODE = B.PLANT_CODE
AND A.ENTITY_CODE = B.ENTITY_CODE AND A.ENTITY_SUB_CODE = B.ENTITY_SUB_CODE
WHERE A.PERSONNEL_ID = @strPerId AND A.PLANT_CODE = @intPlantCode AND B.ENTITY_CODE = 'DPT'

--Retreiving City Name
SET @strCity = ''

SELECT @strCity = B.AREA_DESC FROM GL_PLANT_C A LEFT JOIN GL_AREA_C B ON A.CITY = B.AREA_CODE
WHERE A.PLANT_CODE = @intPlantCode AND B.AREA_TYPE = '03'

--Updating ESIC No., PF No., Designation, Department & City field of TempActualSalary Table
UPDATE TempActualSalary SET ESIC_NO = @strESICNo, PF_NO = @strPFNo, GRADE = @strGrade, DESIGNATION = @strDesignation, DEPARTMENT = @strDepartment, CITY = @strCity
WHERE PERSONNEL_ID = @strPerId
--Updating ESIC No., PF No., Designation, Department field & City of TempActualSalary Table

--Retreiving Present Days
SET @intPresentDays = 0

SELECT @intPresentDays = B.NO_OF_DAYS FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'P' AND
A.TRANS_ID = B.TRANS_ID

--Retreiving Present Days
SET @intAbsentDays = 0

SELECT @intAbsentDays = B.NO_OF_DAYS FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'A' AND
A.TRANS_ID = B.TRANS_ID

--Retreiving Leave Days
SET @intLeaveDays = 0

SELECT @intLeaveDays = B.NO_OF_DAYS FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'L' AND
A.TRANS_ID = B.TRANS_ID

--Retreiving Week Off & Holiday Days
SET @intWODays = 0

SELECT @intWODays = SUM(B.NO_OF_DAYS) FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND (B.STATUS_CODE = 'W' OR
B.STATUS_CODE = 'O') AND A.TRANS_ID = B.TRANS_ID

--Retreiving Week Off Days
SET @intWDays = 0

SELECT @intWDays = SUM(B.NO_OF_DAYS) FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'W'
AND A.TRANS_ID = B.TRANS_ID

--Retreiving Holiday Days
SET @intODays = 0

SELECT @intODays = SUM(B.NO_OF_DAYS) FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'O'
AND A.TRANS_ID = B.TRANS_ID

--Retreiving Without Pay (WP) Days
SET @intWPDays = 0

SELECT @intWPDays = B.NO_OF_DAYS FROM PA_ATTEND_MONTH_H A,
PA_ATTEND_MONTH_D B WHERE A.PERSONNEL_ID = @strPerId AND
A.ATTEND_MODE = 'C' AND A.ATTEND_PERIOD_FROM >= @dtFromDate
AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.STATUS_CODE = 'WP' AND
A.TRANS_ID = B.TRANS_ID

--Retreiving Earned/Encashable Leaves
SET @intEarnedLeaves = 0

SELECT @intEarnedLeaves = (SELECT ISNULL(SUM(A.NO_OF_DAYS),0) FROM PA_LEAVE_AVAIL_ENCASH_T A LEFT JOIN PA_LEAVES_M B ON A.PLANT_CODE = B.PLANT_CODE AND A.LEAVE_CODE = B.LEAVE_CODE
WHERE B.ENCASH = 1 AND A.PERSONNEL_ID = @strPerId AND A.NO_OF_DAYS >= 1
AND (A.AVAIL_FROM BETWEEN @dtFromDate AND @dtToDate) AND (A.AVAIL_TO BETWEEN @dtFromDate AND @dtToDate))

--Retreiving Earned/Encashable Leaves
SET @intEarnedLeavesHQT = 0

SELECT @intEarnedLeavesHQT = (SELECT ISNULL(SUM(A.NO_OF_DAYS),0) FROM PA_LEAVE_AVAIL_ENCASH_T A LEFT JOIN PA_LEAVES_M B ON A.PLANT_CODE = B.PLANT_CODE AND A.LEAVE_CODE = B.LEAVE_CODE
WHERE B.ENCASH = 1 AND A.PERSONNEL_ID = @strPerId AND A.NO_OF_DAYS < 1
AND (A.AVAIL_FROM BETWEEN @dtFromDate AND @dtToDate) AND (A.AVAIL_TO BETWEEN @dtFromDate AND @dtToDate))

--Retreiving Non Earned/Encashable Leaves
SET @intOtherLeaves = 0

SELECT @intOtherLeaves = (SELECT ISNULL(SUM(A.NO_OF_DAYS),0) FROM PA_LEAVE_AVAIL_ENCASH_T A LEFT JOIN PA_LEAVES_M B ON A.PLANT_CODE = B.PLANT_CODE AND A.LEAVE_CODE = B.LEAVE_CODE
WHERE B.ENCASH = 0 AND A.PERSONNEL_ID = @strPerId AND A.NO_OF_DAYS >= 1
AND (A.AVAIL_FROM BETWEEN @dtFromDate AND @dtToDate) AND (A.AVAIL_TO BETWEEN @dtFromDate AND @dtToDate))

--Retreiving Non Earned/Encashable Leaves
SET @intOtherLeavesHQT = 0

SELECT @intOtherLeavesHQT = (SELECT ISNULL(SUM(A.NO_OF_DAYS),0) FROM PA_LEAVE_AVAIL_ENCASH_T A LEFT JOIN PA_LEAVES_M B ON A.PLANT_CODE = B.PLANT_CODE AND A.LEAVE_CODE = B.LEAVE_CODE
WHERE B.ENCASH = 0 AND A.PERSONNEL_ID = @strPerId AND A.NO_OF_DAYS < 1
AND (A.AVAIL_FROM BETWEEN @dtFromDate AND @dtToDate) AND (A.AVAIL_TO BETWEEN @dtFromDate AND @dtToDate))

SET @intTotalWorkingPayDays = (@intPresentDays - @intEarnedLeavesHQT - @intOtherLeavesHQT) + @intODays + (@intEarnedLeaves + @intEarnedLeavesHQT) + (@intOtherLeaves + @intOtherLeavesHQT)

SET @intTotalPayDays = (@intPresentDays - @intEarnedLeavesHQT - @intOtherLeavesHQT) + @intWODays + (@intEarnedLeaves + @intEarnedLeavesHQT) + (@intOtherLeaves + @intOtherLeavesHQT)

SET @intTotalWorkingDays = (@intPresentDays - @intEarnedLeavesHQT - @intOtherLeavesHQT) + @intODays + (@intEarnedLeaves + @intEarnedLeavesHQT) + (@intOtherLeaves + @intOtherLeavesHQT) + @intAbsentDays

SET @intTotalDays = (@intPresentDays - @intEarnedLeavesHQT - @intOtherLeavesHQT) + @intWODays + (@intEarnedLeaves + @intEarnedLeavesHQT) + (@intOtherLeaves + @intOtherLeavesHQT) + @intAbsentDays

--Updating Present Days, Absent Days, Leave Days and Without Pay (WP) Days TempActualSalary Table
UPDATE TempActualSalary SET PRESENT_DAYS = @intPresentDays, ABSENT_DAYS = @intAbsentDays, LEAVE_DAYS = @intLeaveDays,
WP_DAYS = @intWPDays, W_DAYS = @intWDays, O_DAYS = @intODays, WO_DAYS = @intWODays, TOTAL_WRK_PAY_DAYS = @intTotalWorkingPayDays, TOTAL_PAY_DAYS = @intTotalPayDays,
TOTAL_WRK_DAYS = @intTotalWorkingDays, TOTAL_DAYS = @intTotalDays, EARNED_LEAVE = (@intEarnedLeaves + @intEarnedLeavesHQT), OTHER_LEAVE = (@intOtherLeaves + @intOtherLeavesHQT)
WHERE PERSONNEL_ID = @strPerId AND APPLICABLE_FROM = @dtFromDate AND APPLICABLE_TO = @dtToDate
--Updating Updating Present Days, Absent Days, Leave Days and Without Pay (WP) Days TempActualSalary Table

--Retreiving & Updating Monthly Basic Fixed Salary
DECLARE ActualSalaryCur1 CURSOR FOR
SELECT DISTINCT HEAD_CODE FROM PA_SALARY_M

OPEN ActualSalaryCur1

FETCH NEXT FROM ActualSalaryCur1
INTO @strHeadCode

WHILE @@FETCH_STATUS = 0
BEGIN
SET @intFixedAmt = 0

SELECT @intFixedAmt = HEAD_AMOUNT FROM PA_PER_FIXEDSAL_T
WHERE PERSONNEL_ID = @strPerId AND PLANT_CODE = @intPlantCode AND HEAD_CODE = @strHeadCode
AND WEF_DATE = (SELECT MAX(WEF_DATE) FROM PA_PER_FIXEDSAL_T WHERE PERSONNEL_ID = @strPerId AND WEF_DATE <= @dtFromDate AND HEAD_CODE = @strHeadCode)

select @strSql = 'UPDATE TempActualSalary SET FIXED_' + @strHeadCode + ' = ' + CAST(@intFixedAmt AS VARCHAR(20))
+ ' WHERE PERSONNEL_ID = ' + @strPerId + ' AND PLANT_CODE = ' + CAST(@intPlantCode AS VARCHAR(20))
+ ' AND APPLICABLE_FROM = ''' + CONVERT(VARCHAR(20), @DtFromDate, 101) + ''' AND APPLICABLE_TO = '''
+ CONVERT(VARCHAR(20), @DtToDate, 101) + ''''
EXEC (@strsql)

FETCH NEXT FROM ActualSalaryCur1
INTO @strHeadCode
END

CLOSE ActualSalaryCur1
DEALLOCATE ActualSalaryCur1
--Retreiving & Updating Monthly Basic Fixed Salary

--Retreiving Monthly OverTime Salary
SET @intHeadAmt = 0
/* Code commented by fahd on 12 july 2006 as overtime was not getting calculated */

-- SELECT @intHeadAmt = ISNULL(A.OVERTIME,0) * ISNULL(B.RATEPERHOUR,0) FROM PA_ATTEND_MONTH_H A,
-- PA_PER_COMP_SAL_T B WHERE A.ATTEND_MODE = 'C' AND A.PERSONNEL_ID = @strPerId AND
-- A.ATTEND_PERIOD_FROM >= @dtFromDate AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.HEAD_CODE = 'BAS'
-- AND B.PLANT_CODE = @intPlantCode AND A.PERSONNEL_ID = B.PERSONNEL_ID AND
-- A.ATTEND_PERIOD_FROM = B.APPLICABLE_FROM AND A.ATTEND_PERIOD_TO = B.APPLICABLE_UPTO
/*Code starts here*/

SELECT @intHeadAmt = ISNULL(B.Amount,0) FROM PA_ATTEND_MONTH_H A,
PA_PER_COMP_SAL_T B WHERE A.ATTEND_MODE = 'C' AND A.PERSONNEL_ID = @strPerId AND
A.ATTEND_PERIOD_FROM >= @dtFromDate AND A.ATTEND_PERIOD_TO <= @dtToDate AND B.HEAD_CODE = 'OT'
AND B.PLANT_CODE = @intPlantCode AND A.PERSONNEL_ID = B.PERSONNEL_ID AND
A.ATTEND_PERIOD_FROM = B.APPLICABLE_FROM AND A.ATTEND_PERIOD_TO = B.APPLICABLE_UPTO
/*Code ends here*/
--Updating Monthly OverTime Salary field of TempPaySlip Table
UPDATE TempActualSalary SET OT = @intHeadAmt
WHERE PERSONNEL_ID = @strPerId AND PLANT_CODE = @intPlantCode
AND APPLICABLE_FROM = @dtFromDate AND APPLICABLE_TO = @dtToDate
--End Updating Monthly Rate field of TempPaySlip Table

--Updating Daily Attendance
SET @I = 1
WHILE @I <= 31
BEGIN
SET @intDay = 0

SELECT @intDay = CASE WHEN ATTEND_STATUS = 'P' THEN 1 ELSE 0 END FROM PA_ATTENDENCE_H
WHERE PERSONNEL_ID = @strPerId AND DAY(ATTEND_DATE) = @I AND ATTEND_DATE >= @dtFromDate
AND ATTEND_DATE <= @dtToDate

SELECT @strSql = 'UPDATE TempActualSalary SET DAY'+CONVERT(VARCHAR(2),@I)+' = ' +CAST(@intDay AS VARCHAR(20))
+ ' WHERE PERSONNEL_ID = ' + @strPerId -- + ' AND DAY(APPLICABLE_FROM) = ' + CONVERT(VARCHAR(2),@I)
EXEC(@strsql)

SET @I = @I + 1
END

--Fetching Next Employee Id
FETCH NEXT FROM ActualSalaryCur
INTO @strPerId, @intPlantCode, @DtFromDate, @DtToDate
END
CLOSE ActualSalaryCur
DEALLOCATE ActualSalaryCur
--End Updating ESIC No., PF No., Designation, Department & City field of TempActualSalary Table

GO

do reply

With Best Regards
Ruby

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 09:27:07

From your first post, it seems that the number of chacters exceeded 8000 characters
Why dont you use alternate methods suggested in the link I posted?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 10:42:55

You got the csv string wrong.

Try with a shorter csv string and if it is fine, extend it.
Something like this


exec ProcActualSalary 
     @intCompanyCode = 1100,
     @intPlantCode   = 1110,
     @DtSalFrom      = '20070501'
     @DtSalTo        = '20070531'
     @strPersonnelID1 = '90801083,90801143',
     @strPersonnelID2 = '90801144,90801145',
     @strPersonnelID3 = '90801144,90801145'

ruby_sqlserver
Starting Member

24 Posts

Posted - 2007-06-13 : 07:04:22

Khtan

u r right if i select the range of personnel_id till 90801765 then its running properly
and if i seperately run range from 90801766 onwards then also its running properly

so i think i must increase the range from 8000 to some more 10000

if u say

Do tell me wat can be worthful solution as i want whole range to be selected properly

Guide me