Stored Procedure help

[BB69]

Hello,

I recieved this stored procedure from our supplier of our timesheet software but it is not working correctly. They provided this as a courtesy but to get it working, they would want to charge a consulting fee. I broke it down into parts in a separate query and I get the error Incorrect syntax near ')' at the end of the while exists loop. The next portion works (SELECT @PSH_KEY=PSH_KEY, etc) when I put it in an individual query.

Is there a reason for the error? Also, the while exists loop seems to do the exact same thing as the next portion of select statements. Is the while exists loop necessary?

Thanks for any help
Brian

USE [tcworking]
GO
/****** Object: StoredProcedure [dbo].[SP_BANK4_ACCRUE] Script Date: 11/21/2012 13:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--DROP PROCEDURE [dbo].[SP_BANK4_ACCRUE]

ALTER PROCEDURE [dbo].[SP_BANK4_ACCRUE] AS
BEGIN
/* Procedure to accrue the Bank time hours into EMP_BNK4. */

DECLARE @EMP_KEY numeric(11,0)
DECLARE @PSH_KEY numeric(11,0 )
DECLARE @FLEX_HRS numeric(15,2)
DECLARE @TOTAL_HRS numeric(15,2)

WHILE EXISTS(
SELECT PSH_KEY, EMP_KEY, (SUM(PSD_MIN)/60) AS TOTAL_HRS, (SUM(PSD_MIN)-2400) / 60 AS FLEX_HRS
FROM PSDETAIL, PSLINES, PSHEADER, EMPLOYEE, EMPHIS
WHERE (PSHEADER.PSH_ACCBNK1 <> 1 OR PSHEADER.PSH_ACCBNK1 IS NULL)
AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSH_EMH=EMH_KEY AND EMH_EMP=EMP_KEY
GROUP BY PSH_KEY, EMP_KEY
HAVING SUM(PSD_MIN) > 2400)

BEGIN

SELECT @PSH_KEY=PSH_KEY, @EMP_KEY=EMP_KEY, @TOTAL_HRS=(SUM(PSD_MIN)/60),@FLEX_HRS=(SUM(PSD_MIN)-2400) / 60
FROM PSDETAIL, PSLINES, PSHEADER, EMPLOYEE, EMPHIS
WHERE (PSHEADER.PSH_ACCBNK1 <> 1 OR PSHEADER.PSH_ACCBNK1 IS NULL)
AND PSD_PSL=PSL_KEY AND PSL_PSH=PSH_KEY AND PSH_EMH=EMH_KEY AND EMH_EMP=EMP_KEY
GROUP BY PSH_KEY, EMP_KEY
HAVING SUM(PSD_MIN) > 2400

BEGIN TRANSACTION
UPDATE EMPLOYEE
SET EMP_BNK4 = ISNULL(EMP_BNK4,0) + @FLEX_HRS WHERE EMP_KEY = @EMP_KEY

UPDATE PSHEADER SET PSH_ACCBNK1 = 1 WHERE PSH_KEY = @PSH_KEY
IF @@ERROR <> 0
ROLLBACK
ELSE
COMMIT

END
END

 

[SQLBill]

Does this give any errors:

SELECT PSH_KEY, EMP_KEY, (SUM(PSD_MIN)/60) AS TOTAL_HRS, (SUM(PSD_MIN)-2400) / 60 AS FLEX_HRS
FROM PSDETAIL, PSLINES, PSHEADER, EMPLOYEE, EMPHIS
WHERE (PSHEADER.PSH_ACCBNK1 <> 1 
   OR PSHEADER.PSH_ACCBNK1 IS NULL)
   AND PSD_PSL=PSL_KEY 
   AND PSL_PSH=PSH_KEY
   AND PSH_EMH=EMH_KEY
   AND EMH_EMP=EMP_KEY
GROUP BY PSH_KEY, EMP_KEY
HAVING SUM(PSD_MIN) > 2400

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

I'm not positive, but I think that COMMIT needs to be a COMMIT TRANSACTION.

Also, the first select just checks that something exists. The second select actually does something with it.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[BB69]

It does not give me any error. I get the results I am looking for in the tables. It is only when the while exists is added.


Brian

 

[BB69]

OK. I understand why the while exists didn't work when I put it in an individual query. I needed to add the begin and end statements after.

The error I get when I try to process the stored procedure in the application is:
Update Flextime Bank Hours failed with the message :
Invalid class typecast

Any ideas what that error could be caused by?

Brian

 

[litton1]

Could it be bad data?

Does this col have null in the Databse?

PSHEADER.PSH_ACCBNK1 <> 1

Age is a consequence of experience

 

[BB69]

Yes there is a null in the column but it should check for that as the code is

(PSHEADER.PSH_ACCBNK1 <> 1 OR PSHEADER.PSH_ACCBNK1 IS NULL)

 

[IanWaterman]

Usually best to check for NULLS first, try

(PSHEADER.PSH_ACCBNK1 IS NULL OR PSHEADER.PSH_ACCBNK1 <> 1)

Ian

 

[litton1]

Very good point IanWaterman. That way the second part of the OR will not be evaluated

Age is a consequence of experience

 

[BB69]

Ok. It still does not work in their app but I tried executing it manually from SSMS and it works correctly. It seems to be with their app interacting with the SP. I will try to get support throught them.

Thanks for everyone's help.

Brian