February 14, 2008 at 2:42 pm
This is one of those “simple” issues that is driving me crazy.
The following is for when the user enters a TS step that is not supposed to have units. It works.
IF @PayFormulaCode = 'TS' AND @Units <> 0
Begin
set @Message = 'Units Not Allowed With Time Studied Step'
return 1
End
The following is for when the user enters a TS step without units, which is proper. The sproc should look in tbl_Clients_ClientTimeStudy to find a TS record for the client and the specified step. If it exists, then pull some info. If no TS record exists for the client and the specified step, give an error msg.
I keep getting “incorrect syntax near keyword ELSE.” I have tried numerous configurations with similar results. There is more to the sproc and I have verified that for every BEGIN, there is an END.
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units = NULL
BEGIN --first BEGIN
IF EXISTS(SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE @Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND @StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID)
END --for first BEGIN above
ELSE
--Time study record does not exist. tell user. Rollback.
BEGIN --second begin
set @Message = 'No Time Study Record.'
--rollback
END ---for second begin
--end ---<<<not needed
Any input or information to fix the above is appreciated.
Thanks,
Bill
February 14, 2008 at 3:02 pm
If exists should have a begin and end too. It is much easier to see this when you format the code, to make it look purtee 😀
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units = NULL
BEGIN --first BEGIN
IF EXISTS(SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE @Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND @StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID)
BEGIN
END
END --for first BEGIN above
ELSE
--Time study record does not exist. tell user. Rollback.
BEGIN --second begin
set @Message = 'No Time Study Record.'
--rollback
END ---for second begin
--end ---<<<not needed
February 14, 2008 at 4:22 pm
Adam,
What you said makes sense. I am almost there. This is the original code with the added code noted.
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units = NULL
BEGIN --first BEGIN
IF EXISTS(SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE @Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND @StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID)
BEGIN --2nd BEGIN --***just added code starts here
SELECT @PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Clients_ClientTimeStudy
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
tbl_Clients_ClientBasicInformation.Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END --for 2nd BEGIN --***just added code stops here
END --for first BEGIN above
ELSE --Time study record does not exist. tell user. Rollback.
BEGIN --3rd begin
set @Message = 'No Time Study Record.'
--rollback
END ---for 3rd begin
--end ---<<<not needed? If I keep this 'end', i get incorrect syntax near IF (the following IF noted below). If I take it out,
--then I get "tbl_Clients_ClientBasicInformation.Client_PK_ID"
--could not be bound."
---=====Following pull State minimum wage rate
IF @PayFormulaCode = 'SM'
BEGIN
SELECT @StateMinimumWage =
tbl_Admin_GlobalValues.StateMinimumWage
FROM
tbl_Admin_GlobalValues
END
--There is quite a bit more code in this sproc and I've counted my BEGINs and ENDs. They look to be of equal amounts. Thoughts?
Thanks.
February 14, 2008 at 5:04 pm
Your begin and ends look to be correct. One thing I see with the code is your are comparing comparing units to null, with an equal operator
OR @Units = NULL
You should change this to OR @Units IS NULL
What line number throws the error?
February 14, 2008 at 5:35 pm
I made the change to IS NULL.
Here is the message that I am now getting after another adjustment or two:
“Msg 156, Level 15, State 1, Procedure TimeSheet_Insert_TimesheetEntry, Line 351.
Incorrect syntax near the keyword 'print'.”
Between the code we have discussed and the above mentioned ‘print’, there are two more IF statements (working) and a CASE select statement (working) of around 25 lines. Plus some notes and info lines. I have a number of Print statements to track the values.
The CASE statement does not have a BEGIN, but when I put an END after it, I get “The multi-part identifier "tbl_Clients_ClientBasicInformation.Client_PK_ID" could not be bound.” Take away the END and I get the “syntax..near print” message.
The total lines right now is about 430.
It seems as if the problem is working it’s way down the code.
The Insert and Values part of my code looks like:
if @Timesheet_PK_ID is null or
@TimeSheet_PK_ID = 0
begin
Insert into tbl_TimeSheetEntry
(
BatchNumber,
LocationNumber,
………..
WagesPaid,
FundingCode
)
values
(
@BatchNumber,
@LocationNumber,
…………….
@WagesPaid,
@FundingCode
)
--Assign New TimeSheet_PK_ID to record just added
Set @TimeSheet_PK_ID = Scope_Identity ()
end
Thanks again.
February 14, 2008 at 5:39 pm
Did your authoring tool / editor, or perhaps copy&paste remove all the indentation?
If not I strongly recommend you keeping the code easy to read.
Just a thought.
John Esraelo
Cheers,
John Esraelo
February 14, 2008 at 5:44 pm
Between the code we have discussed and the above mentioned ‘print’, there are two more IF statements (working) and a CASE select statement (working) of around 25 lines. Plus some notes and info lines. I have a number of Print statements to track the values.
Does this mean this part of the code is working independantly or does it work when the code is run as a batch?
The CASE statement does not have a BEGIN, but when I put an END after it, I get “The multi-part identifier "tbl_Clients_ClientBasicInformation.Client_PK_ID" could not be bound.” Take away the END and I get the “syntax..near print” message.
This sounds like the select case is wrapped in an if statement without a closing end.
Can you post the other IFs?
February 14, 2008 at 6:06 pm
Adam, I thought the best thing to do was to post the complete sproc. I hope that's OK. I have some notes inside the sproc. Any suggestions will be appreciated.
Thanks.
USE [ClientPayroll]
GO
/****** Object: StoredProcedure [dbo].[TimeSheet_Insert_TimesheetEntry] Script Date: 02/14/2008 16:40:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[TimeSheet_Insert_TimesheetEntry]
(---following into the TSE table
@TimeSheet_PK_ID int output,
--following must be inserted into TSE table.
-- during data entry
@BatchNumber varchar (3) output,
@LocationNumber varchar (3) output,
@WorkDate varchar (12) output,
@ClientNumber varchar (5),
@CustomerNumber varchar (4) = null, --Act's don't have Cust#
@JobNumber varchar (5), --With Acts, use '1'
@StepNumber varchar (3), --For Jobs & Acts, 2 digit
@HoursWorked numeric (5,2) output,
@Units numeric (5,1) = NULL output, --if req'd
--following pulled from CLientBasicInfo and JS tbls.
@Client_PK_ID int output,
@StepNumber_PK_ID int output, --Used with PFC
--following pulled from JS table. Into TSE tbl.
@PrevailingWage numeric (6,3) = NULL output,
@Standard numeric (10,2) = NULL output,
--following calc'd based on PFC, then stored. Need to keep for history for TS%. over time, client's TS% might change.
--keeping the following will allow the % to cal'c at a later date.
@WagesPaid numeric (6,3) = NULL output,
--following pulled from ClientBasicInfo. Into TSE tbl.
@FundingCode varchar (3) = NULL output,
--following pulled from JS table. DOES NOT need to be into TSE
@PayFormulaCode varchar (3) = NULL,
--following pulled from JS table. not inserted into TSE tbl
@StepCode varchar (3) = NULL,
--Following wage types are NOT inserted into the Timesheet tbl, but are needed to calc the WagesPaid figure which IS
inserted into the Timesheet tbl.----
@AverageWage numeric (6,3) = NULL,
@FringeWage numeric (6,3) = NULL,
@SpecialWage numeric (6,3) = NULL,
@GuaranteedWage numeric (6,3) = NULL,
@StateMinimumWage numeric (6,3) = NULL,
@FederalMinimumWage numeric (6,3) = NULL,
@GuaranteedStepWage numeric (6,3) = NULL,
--following not inserted, but needed to determine if active or not
@WorkStatusCode varchar (3) = NULL,
-------------------------
@Customer_PK_ID int = NULL,
@JobNumber_PK_ID int = NULL,
@TimeStudy_PK_ID int = NULL output,
@TimeStudyPercent numeric (5,3) = NULL,
@Message varchar (30) output
)
as
declare
@continue bit
--set all values below
set @continue = 0
set @Message = ''
set @TimeSheet_PK_ID = 0
---the ONLY fields that the data entry person should enter are the
--BatchNumber, LocationNumber, WorkDate (entered once per Batch,
--Location and Date)
--ClientNumber, CustomerNumber, JobNumber, StepNumber, Hours and Units.
--NOTE: No customer # for activities
--Based on the above Client, Customer etc. values, the SProc
--will pull the PK_IDs for the ClientNumber,
--CustomerNumber,JobNumber, and StepNumbers--
--PLUS
--The Clients Average and other Hourly type Wages if req’d
--The Prevailing Wage, if req’d, for the step
--The Client’s TimeStudy percent is needed to calc the WagesPaid #.
---But it does not need to be stored.
--Based upon the PFC of 'TS', divide WagesPaid by the
--PW to get the % when needed for reporting.
--The PieceRate will not be stored. It will be calc'd when needed
--FundingCode for the particular client. IS REQUIRED for all clients. weed out AB (absent hours) during reporting
IF @CustomerNumber IS NULL
BEGIN
SELECT @CustomerNumber =
tbl_Admin_GlobalValues.CompanyNumber
FROM
tbl_Admin_GlobalValues
END
SELECT @StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID,
@StepCode = tbl_Jobs_JobSteps.StepCode,
@PayFormulaCode =
tbl_Jobs_JobStepAndPayFormulaCodes.PayFormulaCode
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Jobs_JobNumber
ON
tbl_Jobs_JobSteps.JobNumber_PK_ID =
tbl_Jobs_JobNumber.JobNumber_PK_ID
JOIN
tbl_Customers_CustomerBasicInformation
ON
tbl_Jobs_JobNumber.Customer_PK_ID =
tbl_Customers_CustomerBasicInformation.Customer_PK_ID
JOIN
tbl_Jobs_JobStepAndPayFormulaCodes
ON
tbl_Jobs_JobStepAndPayFormulaCodes.StepCode =
tbl_Jobs_JobSteps.StepCode
where
tbl_Customers_CustomerBasicInformation.CustomerNumber =
@CustomerNumber
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobSteps.StepNumber = @StepNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber
SELECT @Client_PK_ID =
tbl_Clients_ClientBasicInformation.Client_PK_ID,
@LocationNumber =
tbl_Clients_ClientBasicInformation.LocationNumber,
@FundingCode =
tbl_Clients_ClientFundingAndProgram.FundingCode
--@WorkStatusCode =
--tbl_Clients_ClientWorkStatus.WorkStatusCode
FROM tbl_Clients_ClientBasicInformation
JOIN
tbl_Clients_ClientFundingAndProgram
ON
tbl_Clients_ClientBasicInformation.Client_PK_ID =
tbl_Clients_ClientFundingAndProgram.Client_PK_ID
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber
AND
tbl_Clients_ClientBasicInformation.LocationNumber =
@LocationNumber
---following giving correct msg, but still inserting into TSE tbl
IF @WorkStatusCode = 'I' OR @WorkStatusCode = 'T'
BEGIN
SET @Message = 'Client Is Not Active.'
SET @continue = 0
END
--NOTE: Using the theory of "If you are not going to use the
-- value in the variable, then do not load the variable
--with the value."
--======Following looks at PFC and uses proper calc formula
IF @PayFormulaCode = 'NO' --is for StepCode 'AB' absent hours
BEGIN ---there is no pay for AB
SET @WagesPaid = 0
END
----Following all pull a rate from tbl_Clients_ClientWageRates
IF @PayFormulaCode = 'AW'
BEGIN
SELECT @AverageWage =
tbl_Clients_ClientWageRates.AverageWage
FROM
tbl_Clients_ClientWageRates
WHERE
@Client_PK_ID = tbl_Clients_ClientWageRates.Client_PK_ID
END
IF @PayFormulaCode = 'FW'
BEGIN
SELECT @FringeWage =
tbl_Clients_ClientWageRates.FringeWage
FROM
tbl_Clients_ClientWageRates
WHERE
@Client_PK_ID = tbl_Clients_ClientWageRates.Client_PK_ID
END
IF @PayFormulaCode = 'GW'
BEGIN
SELECT @GuaranteedWage =
tbl_Clients_ClientWageRates.GuaranteedWage
FROM
tbl_Clients_ClientWageRates
WHERE
@Client_PK_ID = tbl_Clients_ClientWageRates.Client_PK_ID
END
IF @PayFormulaCode = 'SW'
BEGIN
SELECT @SpecialWage =
tbl_Clients_ClientWageRates.SpecialWage
FROM
tbl_Clients_ClientWageRates
WHERE Client_PK_ID =
tbl_Clients_ClientWageRates.Client_PK_ID
END
-----------------------------
--following pulls a # from the tbl_Jobs_JobSteps.
IF @PayFormulaCode = 'GS'
BEGIN
SELECT @GuaranteedStepWage =
tbl_Jobs_JobSteps.GuaranteedStepWage
FROM
tbl_Jobs_JobSteps
where @StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
END
IF @PayFormulaCode = 'PR'
BEGIN --pull PW and STD
SELECT @PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage,
@Standard =
tbl_Jobs_JobSteps.Standard,
@GuaranteedStepWage =
tbl_Jobs_JobSteps.GuaranteedStepWage
FROM
tbl_Jobs_JobSteps
where @StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
END
IF @PayFormulaCode = 'TS' AND @Units <> 0
Begin
set @Message = 'Units Not Allowed With Time Studied Step'
----need to bail out here????
return 1
End
---tbl_Clients_ClientTimeStudy is a JOIN table between
---the Client table and the Steps table.
---a record must be in each b/f one can be added
--- to the tbl_Clients_ClientTimeStudy.
---------------------------------------------
IF @PayFormulaCode = 'TS' AND @Units = 0 OR @Units IS NULL
BEGIN --first BEGIN
IF EXISTS(SELECT * FROM tbl_Clients_ClientTimeStudy
WHERE @Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND @StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID)
BEGIN --2nd BEGIN
SELECT @PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Clients_ClientTimeStudy
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
tbl_Clients_ClientBasicInformation.Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END --for 2nd BEGIN
END --for first BEGIN above
ELSE --Time study record does not exist. tell user. Rollback.
BEGIN --3rd begin
set @Message = 'No Time Study Record.'--rollback
END ---for 3rd begin
--end ---<<<not needed? If I keep this 'end', i get incorrect syntax
-- near IF (the following IF noted below). If I take it out,
---then I get "tbl_Clients_ClientBasicInformation.Client_PK_ID"
--could not be bound."
---=====Following pull State and Fed minimum wage rates
---going to work??
if @PayFormulaCode = 'SM'
BEGIN
SELECT @StateMinimumWage =
tbl_Admin_GlobalValues.StateMinimumWage
FROM
tbl_Admin_GlobalValues
END
IF @PayFormulaCode = 'FM'
BEGIN
SELECT @FederalMinimumWage =
tbl_Admin_GlobalValues.FederalMinimumWage
FROM
tbl_Admin_GlobalValues
END
SELECT @WagesPaid =
CASE @PayFormulaCode
when 'NO' THEN
0
When 'AW' THEN
@AverageWage * @HoursWorked
when 'FW' then
@FringeWage * @HoursWorked
when 'SM' then
--(tbl_Admin_GlobalValues.StateMinimumWage * @HoursWorked)
@StateMinimumWage * @HoursWorked
when 'FM' then
@FederalMinimumWage * @HoursWorked
when 'SW' then
@SpecialWage * @HoursWorked
when 'RW' then
@AverageWage * @HoursWorked
when 'GW' then --currently .59 --pay for 1-80, 81, 82
@GuaranteedWage * @HoursWorked
when 'GS' then
@GuaranteedStepWage * @HoursWorked
--following from the tbl_Clients_ClientTimeStudy and then calc'ng
when 'TS' then
((@TimeStudyPercent * @PrevailingWage) * .01) * @HoursWorked
when 'PR' then
(@Units *(@PrevailingWage/@Standard)) --OR @Units is null)
---need a formula for PayCorrection 'PC'???
--end
/*
begin
set
@Message = 'Units needed'
end
--else
--begin
--set @Units = @Units
--end
--end
-------------------
*/
print @Client_PK_ID
print 'Work Status ' + COALESCE(@WorkStatusCode, '')
print 'Client # ' + COALESCE(@ClientNumber,'')
print 'Customer # ' + COALESCE(@CustomerNumber,'')
print 'Job # ' + COALESCE(@JobNumber,'')
print 'Step # ' + COALESCE(@StepNumber,'')
print 'Location #' + COALESCE(@LocationNumber,'')
--print 'Prevailing ' + cast(@PrevailingWage as varchar)
print 'TS % = ' + Convert(varchar (10), ISNULL(@TimeStudyPercent,0))
print 'Prevailing ' + Convert(varchar (10), ISNULL(@PrevailingWage,0))
print 'Guar Wage ' + Convert(varchar (10), ISNULL(@GuaranteedWage,0))
--print 'Prevailing ' + Coalesce(@PrevailingWage, '@PrevailingWage = NULL')
print 'GuarStep Wage ' + Convert(varchar (10), ISNULL(@GuaranteedStepWage,0))
--print 'Prevailing ' + Coalesce(@PrevailingWage, '')
print 'State MW ' + Convert(varchar (10), ISNULL(@StateMinimumWage,0))
print 'Fed MW ' + Convert(varchar (10), ISNULL(@FederalMinimumWage,0))
print 'AW = ' + Convert(varchar (10), ISNULL(@AverageWage,0))
print 'FW = ' + Convert(varchar (10), ISNULL(@FringeWage,0))
print 'SW = ' + Convert(varchar (10), ISNULL(@SpecialWage,0))
print 'WagesPaid = ' + Convert(varchar (10), ISNULL(@WagesPaid,0))
print 'Standard = ' + Convert(varchar (10), ISNULL(@Standard, 0))
print 'Funding Code = ' + COALESCE(@FundingCode,'')
print 'Pay Formula Code = ' + COALESCE(@PayFormulaCode,'')
/*
--select @f,convert(numeric(38,2),@f),
--convert(varchar(66),convert(numeric(38,2),@f))
print 'pw' + (Select @PrevailingWage,
Convert(numeric (6,3), @PrevailingWage),
Convert(varchar (8), convert(numeric(6,3),@PrevailingWage))
*/
--@MinimumWage = MinimumWage,
--@MinimumGuaranteedWage = MinimumGuaranteedWage,
if @Timesheet_PK_ID is null or
@TimeSheet_PK_ID = 0
begin
Insert into tbl_TimeSheetEntry
(BatchNumber,
LocationNumber,
WorkDate,
Client_PK_ID,
StepNumber_PK_ID,
TimeStudy_PK_ID,
HoursWorked,
Units,
PrevailingWage,
--Standard,
WagesPaid,
FundingCode
--PayFormulaCode
)
values
(@BatchNumber,
@LocationNumber,
@WorkDate,
@Client_PK_ID,
@StepNumber_PK_ID,
@TimeStudy_PK_ID,
@HoursWorked,
@Units,
@PrevailingWage,
--@Standard,
@WagesPaid,
@FundingCode
--@PayFormulaCode
)
--Assign New TimeSheet_PK_ID to record just added
Set @TimeSheet_PK_ID = Scope_Identity ()
end
February 14, 2008 at 9:51 pm
All the syntax looked good except that in the CASE statement @PayFormulaCode you are missing the end. I think this is the one you were speaking of before. You definitely need the end here.
Your problem is exactly what sql is telling you.
Look at the code you posted. The table tbl_Clients_ClientBasicInformation is never referenced in the from clause.
BEGIN --2nd BEGIN --***just added code starts here
SELECT @PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Clients_ClientTimeStudy
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
tbl_Clients_ClientBasicInformation.Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END --for 2nd BEGIN --***just added code stops here
February 15, 2008 at 12:39 am
OT
As you will have noticed, readability is always an issue :doze:
Start using table_aliases in your queries !
e.g.
select you_know_a_schema_name_can_be_very_long_indeed.wel_you_know_an_object_name_can_be_very_long_too .mycolumn
from you_know_a_schema_name_can_be_very_long_indeed.wel_you_know_an_object_name_can_be_very_long_too
inner join the_other_schema.the_other_object_name
on you_know_a_schema_name_can_be_very_long_indeed.wel_you_know_an_object_name_can_be_very_long_too .PK = the_other_schema.the_other_object_name.theFKcol
...
will become
select A.mycolumn
from you_know_a_schema_name_can_be_very_long_indeed.wel_you_know_an_object_name_can_be_very_long_too
A
inner join the_other_schema.the_other_object_name B
on A.PK = B.theFKcol
...
Don't just use A or B for table_aliases , but make them meaningfull !
e.g. HR.person might be aliassed as P in your query
HR.salary would become S
Objects references more than once, wel ... off we go ...
HR.Wage W_Boss -- wages of bosses
HR.Wage W_clerk -- use a meaningfull logical name in your queries
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2008 at 3:43 pm
ALZDBA,
Good point. Once I get it working, I’ll do it.
Adam,
As I mentioned before, it looks like the problem is moving down the sproc and I believe it’s just about out. After correcting my code per your advice, the error is now:
“Msg 102, Level 15, State 1, Procedure TimeSheet_Insert_TimesheetEntry, Line 430
Incorrect syntax near 'END',” which puts it in the Values section at @PrevailingWage. I know that this is an approximate.
I figured that it was the END after Set @TimeSheet_PK_ID ….. , but when I remove this END, then I get : “….Line 429 Incorrect syntax near ')'.”
I’ve checked my table name for the Insert, verified the order of the columns with the Insert, Values and underlying table. I’ve checked for commas and don’t see any missing. It’s in the section below (I think). Thanks again.
if @Timesheet_PK_ID is null or
@TimeSheet_PK_ID = 0
BEGIN
Insert into tbl_TimeSheetEntry
(BatchNumber,
LocationNumber,
WorkDate,
Client_PK_ID,
StepNumber_PK_ID,
TimeStudy_PK_ID,
HoursWorked,
Units,
PrevailingWage,
WagesPaid,
FundingCode
)
values
(@BatchNumber,
@LocationNumber,
@WorkDate,
@Client_PK_ID,
@StepNumber_PK_ID,
@TimeStudy_PK_ID,
@HoursWorked,
@Units, ---<<Errors pointing here
@PrevailingWage, ---<<and here
@WagesPaid,
@FundingCode
)
--Assign New TimeSheet_PK_ID to record just added
Set @TimeSheet_PK_ID = Scope_Identity ()
END
February 15, 2008 at 5:25 pm
This segment of code looks right. I would work my way through the code testing it piece by piece, from the top to the bottom. This way you can see exactly where your code is being parsed wrong.
Code is much easier to manage when you test chuncks rather one fell swoop.
February 18, 2008 at 12:23 am
As Adam stated, the missing end statement with your case
statement is actualy the only thing I can find that would break your code.
I used SQL Refactor to parse it. Nice tool to make something readable 😎
Best parctise is :
- to start your sproc with a "begin" and end it with an "end"
- add "set nocount on" at the beginning of the procedure because
it only causes network traffic for which many of us don't have any
use at all. (* row(s) affected) most of the time isn't used !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply