June 23, 2003 at 11:49 pm
Hi all
Im having a bad brain day today..
declare @vmyobj varchar(100)
set @vmyobj = '''orders'',''syscomments'',''sysobjects'''
print @vmyobj
-- prints this:
-- 'orders','syscomments','sysobjects'
select *
from master..FGMapperPageAlloc
wheredbid = 6 and fileid = 1
andObjectNameAtPage in (@vmyobj)
order by 1,2
returns nothing..??
of course, a single obj works fine, like 'sysobjects' for example, I cant understand why though(?), I would have thought the parm substition and binding of the variable would have worked a treat..
andObjectNameAtPage in ('orders','syscomments','sysobjects')
works perfectly...
hmm
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 24, 2003 at 1:44 am
It doesn't work because SQL thinks the contents of your variable is a single item in the IN clause. i.e. it is searching for ''orders'',''syscomments'',''sysobjects''
If you create the whole select statement and execute it dynamically it should work i.e.
set @sql = 'select *
from master..FGMapperPageAlloc
where dbid = 6 and fileid = 1
and ObjectNameAtPage in (' + @vmyobj + ')
order by 1,2'
exec (@sql)
June 24, 2003 at 2:49 am
hmm.. i was trying to get out of that 🙂 but no matter, thanks for the post.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 24, 2003 at 7:38 am
You could try
select *
from master..FGMapperPageAlloc
where dbid = 6 and fileid = 1
and charindex(ObjectNameAtPage,@vmyobj_) > 0
order by 1,2
Sorry, typo.
Edited by - davidburrows on 06/24/2003 07:41:11 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply