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.

In addition, why is it that when I am using
dynamic SQL, some of the string will be truncated

DECLARE @execstr varchar(4000)

SET @execstr = 'DECLARE ....'+
'....'+
'.....'

SELECT @execstr


Some of the string will be display. Please advise and comment,
tks




>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.