Problems with the IN Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problems with the IN Clause

Author

Topic

sippon77
Starting Member

13 Posts

Posted - 2005-03-14 : 08:23:43

hi,

i am having problems with the following query,

Declare @vchStates as nvarchar(100)
set @vchstates = 'TX,CA,CO'
set @vchstates = REPLACE(@vchstates,',',''',''')
set @vchstates = '''' + @vchstates + ''''
print @vchstates
select iw.iworknoteid,iw.iincidentid,c.vchCompanyname,i.vchDesc1 'Incident Desc',
iw.vchWorknote1 + isNull(iw.vchworknote2,'') + isNull(iw.vchworknote3,'') + isNull(iw.vchworknote4,'') 'Incident WorkNotes',
iw.vchenteredBy, i.dtInsertdate
from incidentworknote iw
join incident i
on iw.iIncidentid = i.iIncidentid
and i.iIncidentCategory in (2,3)
and iw.vchWorknote1 <> ''
join company c
on i.iownerid = c.iCompanyid and
c.ICOMPANYTYPECODE = 101445 and
c.chregioncode in (@vchstates)

When i execute the above query i get no records as output(there are no error messages!!). The problem is with the @vchstates variable that i am using in the IN clause. If i replace the @vchstates variable with ('TX','CA','CO') then i get output records.
It would be helpful if somebody could tell me whats wrong or how to use a string in the IN clause

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-14 : 08:31:00

http://www.sqlteam.com/item.asp?ItemID=11499

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources