February 28, 2008 at 3:32 pm
I need another pair of eyes to please look at this. I believe it is a problem of not assigning the value to the @TimeSheet_PK_ID parameter. But I’m not seeing it.
When I use PRINT, I am seeing the other values in the WHERE statement, but not the TimeSheet_PK_ID value. Results is giving me “0” for the TimeSheet_PK_ID.
When I enter the info for a record that does not exist in tbl_TimeSheetEntry, the ELSE code does kick in. I know the answer is in front of me, but I’m not seeing it.
As always, thanks.
BEGIN TRY
IF EXISTS(
SELECT
@TimeSheet_PK_ID -- = TimeSheet_PK_ID
--if I leave out the "= TimeSheet_PK_ID", there is
--no error, but also no selection from tbl_TimeSheetEntry.
--If I put it in, then there is an "incorrect syntax near
-- = " error and also near "ELSE".
--
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber =
tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_TimeSheetEntry
SET HoursWorked = coalesce(@HoursWorked, HoursWorked),
Units = coalesce(@Units, Units)
WHERE
@TimeSheet_PK_ID =
tbl_TimeSheetEntry.TimeSheet_PK_ID
---tried this. didn't work. don't think I need it.
AND
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
END
ELSE
BEGIN
IF NOT EXISTS(
SELECT
TimeSheet_PK_ID
-–tried with and w/o the @TimeSheetEntry_PK_ID
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber =
tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
SET @Message = 'Time Sheet Record Does Not Exist'
Return 1
END
END
COMMIT
END TRY
February 28, 2008 at 8:32 pm
Your first Exists should start
IF EXISTS(SELECT * from ...
You cannot assign a variable within an EXISTS.
Since you are not setting @TimeSheet_PK_ID, your WHERE clause for the UPDATE should start WHERE @BatchNumber = tbl_TimeSheetEntry.BatchNumber etc, not
WHERE @TimeSheet_PK_ID = tbl_TimeSheetEntry.TimeSheet_PK_ID otherwise nothing will be updated.
Do you have a CATCH? Missing one when you have a TRY will produce errors.
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
February 29, 2008 at 6:54 am
Your first Exists should start
IF EXISTS(SELECT * from ...
You cannot assign a variable within an EXISTS.
This is absolutely true. An if exists checks to make sure the statement within is valid, it actually ignores the column list once it retreives the metadata.
You will want to set the variable after the if exists. Do this and let us know how it runs.
February 29, 2008 at 4:58 pm
When I use the following, it still doesn’t work
BEGIN TRY
IF EXISTS(
SELECT *
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_TimeSheetEntry
SET HoursWorked = coalesce(@HoursWorked, HoursWorked),
Units = coalesce(@Units, Units)
WHERE
@TimeSheet_PK_ID =
tbl_TimeSheetEntry.TimeSheet_PK_ID
END
ELSE
BEGIN
IF NOT EXISTS(
SELECT *
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
SET @Message = 'Time Sheet Record Does Not Exist'
Return 1
END
END
COMMIT
END TRY
With the following, it does work, but the first part below seems redundant in my coding.
SELECT @TimeSheet_PK_ID = TimeSheet_PK_ID
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
[/code]
Code continues.
BEGIN TRY
IF EXISTS(
SELECT *
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_TimeSheetEntry
SET HoursWorked = coalesce(@HoursWorked, HoursWorked),
Units = coalesce(@Units, Units)
WHERE
@TimeSheet_PK_ID =
tbl_TimeSheetEntry.TimeSheet_PK_ID
END
ELSE
BEGIN
IF NOT EXISTS(
SELECT *
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
)
BEGIN
SET @Message = 'Time Sheet Record Does Not Exist'
Return 1
END
END
COMMIT
END TRY
If I understand correctly, I need to use the “*” in “IF EXISTS( SELECT * “ because the uniqueness of the record is made up of multiple columns (the BatchNumber, LocationNumber, etc.) and NOT just one value as in the following different sproc:
IF EXISTS( ---this snippet does work.
SELECT ---using Client_PK_ID instead of '*'
Client_PK_ID
FROM
tbl_Clients_ClientWageRates
WHERE
@Client_PK_ID =
tbl_Clients_ClientWageRates.Client_PK_ID)
I believe I’ve seen code with “SELECT 1”. What does the 1 stand for?
Below is my current CATCH.
BEGIN CATCH
---ERROR INFO
DECLARE @ErrMsg varchar(1000),
@ErrSeverity int ---other error codes??
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()---KEEP FOR ASP
RAISERROR(@ErrMsg, @ErrSeverity, 1)
ROLLBACK TRANSACTION
END CATCH
As always, any help, input or suggestions are appreciated.
Thanks,
Bill
February 29, 2008 at 5:18 pm
The code that doesn't work does not work because @TimeSheet_PK_ID has no value at the time you ask to select...WHERE @TimeSheet_PK_ID = tbl_TimeSheetEntry.TimeSheet_PK_ID.
The part that you say seems redundant only looks redundant. However, you either have to set @TimeSheet_PK_ID as you coded it or do without @TimeSheet_PK_ID altogether by coding
IF EXISTS(SELECT *
FROM
tbl_TimeSheetEntry
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_TimeSheetEntry
SET HoursWorked = coalesce(@HoursWorked, HoursWorked),
Units = coalesce(@Units, Units)
WHERE
@BatchNumber = tbl_TimeSheetEntry.BatchNumber
AND
@LocationNumber = tbl_TimeSheetEntry.LocationNumber
AND
@WorkDate = tbl_TimeSheetEntry.WorkDate
AND
@Client_PK_ID = tbl_TimeSheetEntry.Client_PK_ID
AND
@StepNumber_PK_ID = tbl_TimeSheetEntry.StepNumber_PK_ID
END
You have the wrong reason for using the “*” in “IF EXISTS( SELECT * “. It has nothing to do with the number of columns in the where clause or uniqueness of records. Think of it as saying to the server "use whatever field(s) you think best".
March 1, 2008 at 8:29 am
I believe I’ve seen code with “SELECT 1”. What does the 1 stand for?
Willy this is primarily used because it gives a slight performance boost. If exists evaluates if a row exists that meets the criteria set by the query. The column list is not relevant to SQL finding the record. Therefore, using a static value prevents SQL from looking through the column metadata, thus improving performance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply