Reason why this doesn't work

[dscoiho]

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

 

[SQLBill]

What do you want the results of the SELECT to look like?

-SQLBill

Posting advice: FAQ481-4875

 

[dscoiho]

Sorry forgot that part. I want the final result to have RM1 RM2 and RM4.

 

[SQLBI]

Hi,

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

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!

 

[SQLBI]

Sorry

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!

 

[SQLBill]

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

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