Hi,
Thank for your prompt reply.
So, it mean that I can't apply the db change to my SP alone.
BTW, any good way to avoid using cursor in SP ?
It really slowing my performance.
>The USE command cannot be used in a stored procedure. It can be used in dynamic SQL but it'll be only in effect during that batch excution. You can combine multiple command in a batch.
>SET @db_name = 'pubs'
>SET @execstr = 'USE '+ @db_name + ' DBCC SHOWCONTIG (authors)'
>EXEC(@execstr)
>
>
>>
>>Normally, in QA, we can switch to another database using the USE command
>>like 'use DBX'.
>>
>>However, we can use it in our normal SP. Therefore, I not sure
>>whether using dynamic SQL to 'switch' database in SP will still work
>>as in QA like
>>
>> SET @execstr = 'USE '+@@db_name
>> EXEC(@execstr)
>>
>>
>>Cos I am using some DBCC CONTIG command to check for index
>>and need to switch to that database to use it.
Best Regards
Virusim