Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reason why this doesn't work

Status
Not open for further replies.

dscoiho

MIS
Sep 26, 2005
51
US
I am using SQL Server 2000

this statement gives me this error "Incorrect syntax near the keyword 'AND'".

DECLARE @TERR1 VARCHAR(15)
SET @TERR1 ='RM1'AND'RM2'AND'RM4'
SELECT RP.TERR1

FROM dbo.REP_PROFILE RP

WHERE RP.TERR1 =@TERR1

I can get this to work for single item but not for multiples.

Doesnt like <IN> either.


DECLARE @TERR1 VARCHAR(15)
SET @TERR1 ='RM1'
SELECT RP.TERR1

FROM dbo.REP_PROFILE RP

WHERE RP.TERR1 =@TERR1
 
What do you want the results of the SELECT to look like?

-SQLBill

Posting advice: FAQ481-4875
 
Sorry forgot that part. I want the final result to have RM1 RM2 and RM4.
 
Hi,

I may be missing something here, but isn't what you're trying to do

Code:
SELECT RP.TERR1

FROM dbo.REP_PROFILE RP

WHERE RP.TERR1 IN (RM1, RM2, RM4)




Cheers,
Leigh

The problem with common sense is that it isn't that common!


 
Sorry

Code:
SELECT RP.TERR1

FROM dbo.REP_PROFILE RP

WHERE RP.TERR1 IN ('RM1', 'RM2', 'RM4')

Cheers,
Leigh

The problem with common sense is that it isn't that common!


 
If you want an 'array' ('RM1', 'RM2', 'RM4'), I believe you need to do it this way:

Code:
DECLARE @TERR1 VARCHAR(15)
DECLARE @SQL VARCHAR(200)
SET @TERR1 ='RM1' + ', ' + 'RM2' + ', ' + 'RM4'
--next line shows what @TERR1 is set as
SELECT @TERR1
--next code part sets the script to be run
SET @SQL = 'SELECT RP.TERR1 FROM dbo.REP_PROFILE RP WHERE RP.TERR1 IN (' + @TERR1 + ')'
--next line is for testing, comment
--out with two dashes when testing is done
PRINT @SQL
--next line is for running, uncomment 
--when the test is successful
--EXEC(@SQL)

-SQLBill

Posting advice: FAQ481-4875
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top