change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:

> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
'
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======
TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many stor
ed
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thin
g
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>|||Ed wrote:[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')