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
donnie91910Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
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?
0
donnie91910Author Commented:
Yes I want to create conditions for the 'IF' statements.
0
donnie91910Author Commented:
I have been trying to fix it for about 3hours and can't figure it out.
Again, help is greatly appreciated.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

tigin44Commented:
do you mean you want to create a where condition? if so you should convert your sql statement to dynamic sql syntax and do add the contitions by using them... The code snippet may guide you to correct your code.


declare @sqlStatement     nvarchar(4000);
 
SELECT @sqlStatement = N'SELECT col1, col2, col3 ' +
                       N'FROM table1 '+
                       N'WHERE 1 = 1 ' 
IF @SearchScope = 1 AND @LinkedProspectID <> 0
BEGIN
  SELECT @sqlStatement = @sqlStatement + N'AND (rh.fldReceivedFrom_Scope = 1 AND rh.fldResID = LTRIM(STR('+@SearchID+ N')) '+
										 N'OR   (rh.fldReceivedFrom_Scope = 3 AND rh.fldProspectID =  LTRIM(STR('+@LinkedProspectID+ N'))'
END 
 
EXEC sp_executesql @sqlStatement

Open in new window

0
Anthony PerkinsCommented:
Unfortunately, you have not told us what you want to do with that segment of code.  If you had at least included a THEN, we would know what you were trying to do.

I other words, supposing one of the rows does meet the conditions that:
@FromOrForScope = 1 and @SearchScope = 1 AND @LinkedProspectID <> 0                               AND (rh.fldReceivedFrom_Scope = 1 AND rh.fldResID = LTRIM(STR(@SearchID))
       OR   (rh.fldReceivedFrom_Scope = 3 AND rh.fldProspectID =  LTRIM(STR(@LinkedProspectID))

What then?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
donnie91910Author Commented:
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.
0
SharathData EngineerCommented:
try to replace your IF-ELSE statements with CASE
0
Anthony PerkinsCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.