Link to home
Start Free TrialLog in
Avatar of donnie91910
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
Avatar of tigin44
tigin44
Flag of Türkiye image

the problem is related to this part of the code..



                  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

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?
Avatar of donnie91910
donnie91910

ASKER

Yes I want to create conditions for the 'IF' statements.
I have been trying to fix it for about 3hours and can't figure it out.
Again, help is greatly appreciated.
SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye 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
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
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
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.
SOLUTION
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
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)

Open in new window