Link to home
Start Free TrialLog in
Avatar of ev72178
ev72178Flag for United States of America

asked on

IF/ELSE statement in stored procedure not working correctly

I want to import a files into a temp table and select a substring of the first record as the FileID. I then want to compare this FileID to the FileID column in my Pharmacy.PDEFileLog table. If there is a match and the UplaodedReturnFile column in my Pharmacy.PDEFileLog table is NULL, I want to update the table with certain values (see code below). If the FileID does not exist, I want to return a statemnt that the file does not exist and if the FileID does exists and the UplaodedReturnFile column in my Pharmacy.PDEFileLog table is 'TRUE', I want to return a statement saying that the file has already  been uploaded. Here is my code:
SELECT SUBSTRING(strRecord, 17, 3) as FileID
FROM #tmpDDPS

IF NOT EXISTS (SELECT FileID, UploadedReturnFile
FROM Pharmacy.PDEFileLog
WHERE FileID = FileID AND UploadedReturnFile='TRUE')

BEGIN
UPDATE Pharmacy.PDEFileLog
SET UploadedReturnFile='TRUE', DateUploadedReturnFile=GETDATE(), UploadedBy=USER
WHERE FileID = FileID
END

ELSE

BEGIN
PRINT 'This file has already been uploaded to the server.'
END

The above code updates all of my records in the Pharmacy.PDEFileLog table if the FileID exists. Not sure where to go from here. Any help would be appreciated. Thx.
Avatar of ptjcb
ptjcb
Flag of United States of America image

>> If the FileID does not exist, I want to return a statement that the file does not exist

Return a statement? What do you want to say in the statement? Where do you want to return it to?

>>>if the FileID does exists and the UplaodedReturnFile column in my Pharmacy.PDEFileLog table is 'TRUE', I want to return a statement saying that the file has already  been uploaded.

Same questions.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial