Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Incorrect syntax near the keyword 'IF' and 'AND'

Posted on 2009-04-17
8
Medium Priority
?
1,780 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:donnie91910
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 24172513
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
 

Author Comment

by:donnie91910
ID: 24172628
Yes I want to create conditions for the 'IF' statements.
0
 

Author Comment

by:donnie91910
ID: 24172656
I have been trying to fix it for about 3hours and can't figure it out.
Again, help is greatly appreciated.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 400 total points
ID: 24172688
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 24172724
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
 

Author Comment

by:donnie91910
ID: 24172787
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 24172886
try to replace your IF-ELSE statements with CASE
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24175709
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question