donnie91910
asked on
Incorrect syntax near the keyword 'IF' and 'AND'
I keep getting these errors in my code. I've tried to find the fix but to no avail. any help is greatly appreciated
Here are the errors.
Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Line 107
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 113
Incorrect syntax near the keyword 'And'.
Msg 156, Level 15, State 1, Line 127
Incorrect syntax near the keyword 'END'.
Errors-in-SQL.txt
Here are the errors.
Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Line 107
Incorrect syntax near the keyword 'AND'.
Msg 156, Level 15, State 1, Line 113
Incorrect syntax near the keyword 'And'.
Msg 156, Level 15, State 1, Line 127
Incorrect syntax near the keyword 'END'.
Errors-in-SQL.txt
ASKER
Yes I want to create conditions for the 'IF' statements.
ASKER
I have been trying to fix it for about 3hours and can't figure it out.
Again, help is greatly appreciated.
Again, help is greatly appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually the 'IF' part of the code was in a dynamic SQL statement. I was trying to make it work with some other code that I had, but I guess that is not going to happen without alot of work. I will need to get back to you.
I think I can modify the dynamic SQL statement where I got the code from in the first place.
Thanks.
I think I can modify the dynamic SQL statement where I got the code from in the first place.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This corrects a few problems and should be easier to read:
Declare @FromOrForScope int, -- 0=NoFiltering, 1=ReceivedFrom, 2=ReceivedFor
@SearchScope int, -- 0=NoFiltering, 1=Resident, 2=ResidentContact, 3=Prospect,
-- 4=ProspectContact, 5=MktgCommunityContact, 6=ThirdPartyContact
@SearchID int, -- IF @SearchID = 0 then all people in that SearchScope for that Campus are returned)
@DateStart smalldatetime, -- Receipt Date Entered Start
@DateEnd smalldatetime, -- Receipt Date Entered End
@InString nvarchar(25) -- Invoice Type
Select @FromOrForScope = 1,
@SearchScope = 6,
@SearchID = 0,
@DateStart = '1/1/2008',
@DateEnd = '5/17/2009',
@InString = '1,2,3'
DECLARE @LinkedProspectID INT
IF @SearchScope = 1
SET @LinkedProspectID = ISNULL((SELECT fldProspectID FROM tblMProspects WHERE fldResID = @SearchID),0)
ELSE
SET @LinkedProspectID = 0
SELECT c.fldCampusID CampusID,
c.fldName Community,
c.fldPhone Community_Phone,
rh.fldReceipt_Num Receipt_Num,
rh.fldReceipt_Date Receipt_Date,
rh.fldReceipt_Amount Receipt_Amount,
rh.fldPaymentID PaymentID,
rh.fldReceivedFrom_Scope,
CASE rh.fldReceivedFrom_Scope
WHEN 1 THEN 'Resident'
WHEN 2 THEN 'Resident Contact'
WHEN 3 THEN 'Prospect'
WHEN 4 THEN 'Prospect Contact'
WHEN 5 THEN 'Mkt Contact'
WHEN 6 THEN 'TrdP Cont'
END ReceivedFromType,
CASE rh.fldReceivedFrom_Scope
WHEN 1 THEN (
SELECT 'Res: ' + RTRIM(fldFirstName) + ' ' + RTRIM(fldLastName)
FROM tblResident
WHERE fldResID = rh.fldResID
)
WHEN 2 THEN (
SELECT 'Res Cont: ' + RTRIM(fldName) + ' ' + RTRIM(fldBusinessName)
FROM tblContactAddressList
WHERE fldContactID = rh.fldContactID
)
WHEN 3 THEN (
SELECT 'Prosp: ' + RTRIM(fldFirstName) + ' ' + RTRIM(fldLastName)
FROM tblMProspects
WHERE fldProspectID = rh.fldProspectID
)
WHEN 4 THEN (
SELECT 'Prosp Cont: ' + RTRIM(fldFirstName) + ' ' + RTRIM(fldLastName)
FROM tblMContactAddressList
WHERE fldContactID = rh.fldMContactID
)
WHEN 5 THEN (
SELECT 'Mkt Cont: ' + RTRIM(fldFirstName) + ' ' + RTRIM(fldLastName)
FROM tblMContactAddressList
WHERE fldContactID = rh.fldMContactID
)
WHEN 6 THEN (
SELECT 'TrdP Cont: ' + RTRIM(fldFirstName) + ' ' + RTRIM(fldLastName)
FROM tblReceipts_3PRContact
WHERE fldReceipts_3PRContactID = rh.fldReceipts_3PRContactID
)
END ReceivedFrom,
CASE rh.fldReceiptScope
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Third Party'
WHEN 3 THEN 'Deposit'
WHEN 4 THEN 'Transfer-To-Invoice'
WHEN 5 THEN 'Prepaid'
END ReceiptType
-- This needs work (you cannot use it this way)
-- IF @FromOrForScope = 1
-- BEGIN
-- IF @SearchScope = 1 AND @LinkedProspectID <> 0
-- BEGIN
-- AND (rh.fldReceivedFrom_Scope = 1 AND rh.fldResID = LTRIM(STR(@SearchID))
-- OR (rh.fldReceivedFrom_Scope = 3 AND rh.fldProspectID = LTRIM(STR(@LinkedProspectID))
-- END
-- ELSE
-- IF @SearchScope > 0
-- BEGIN
-- And rh.fldReceivedFrom_Scope = LTRIM(STR(@SearchScope))
-- IF @SearchScope = 1 AND @SearchID <> 0
-- AND rh.fldResID = LTRIM(STR(@SearchID))
-- IF @SearchScope = 2 AND @SearchID <> 0
-- AND rh.fldContactID = LTRIM(STR(@SearchID))
-- IF @SearchScope = 3 AND @SearchID <> 0
-- AND rh.fldProspectID = LTRIM(STR(@SearchID))
-- IF @SearchScope = 4 AND @SearchID <> 0
-- AND rh.fldMContactID = LTRIM(STR(@SearchID))
-- IF @SearchScope = 5 AND @SearchID <> 0
-- AND rh.fldMContactID = LTRIM(STR(@SearchID))
-- IF @SearchScope = 6 AND @SearchID <> 0
-- AND rh.fldReceipts_3PRContactID = LTRIM(STR(@SearchID))
-- END
-- END
FROM tblReceipts_Header rh
INNER JOIN tblCampus c ON c.fldCampusID = rh.fldCampusID
WHERE rh.fldReceipt_Date Between @DateStart And @DateEnd
--AND rh.fldReceiptScope IN (@InString ) -- This needs work (you cannot use it this way)
IF @FromOrForScope 1
BEGIN
IF @SearchScope = 1 AND @LinkedProspectID <> 0
BEGIN
AND (rh.fldReceivedFrom_Scope = 1 AND rh.fldResID = LTRIM(STR(@SearchID))
OR (rh.fldReceivedFrom_Scope = 3 AND rh.fldProspectID = LTRIM(STR(@LinkedProspectI
END
ELSE
IF @SearchScope > 0
BEGIN
And rh.fldReceivedFrom_Scope = LTRIM(STR(@SearchScope))
IF @SearchScope = 1 AND @SearchID <> 0
AND rh.fldResID = LTRIM(STR(@SearchID))
IF @SearchScope = 2 AND @SearchID <> 0
AND rh.fldContactID = LTRIM(STR(@SearchID))
IF @SearchScope = 3 AND @SearchID <> 0
AND rh.fldProspectID = LTRIM(STR(@SearchID))
IF @SearchScope = 4 AND @SearchID <> 0
AND rh.fldMContactID = LTRIM(STR(@SearchID))
IF @SearchScope = 5 AND @SearchID <> 0
AND rh.fldMContactID = LTRIM(STR(@SearchID))
IF @SearchScope = 6 AND @SearchID <> 0
AND rh.fldReceipts_3PRContactI
END
END
what do you want to do here? produce some conditions? the syntax in this part is incorrect. if you explain what you want to do we may fix it... this does ot help us to understand what you want to do?