August 24, 2001 at 6:19 am
Hi!
I'm new in this group and I have a problem to use the comand, 'use <databasename>' inside a stored procedure.
Someone know how to change the current database inside a SP, if it´s possible?
August 24, 2001 at 8:40 am
only way to reference objects of another database in a proc is to use the full name of the object i.e server.database.user.object
you can possibly use dynamic sql .. i don't know whether it will help , but i've posted an example below
CREATE PROCEDURE Usp_test AS
DECLARE @SqlString nvarchar(200)
SET @SqlString = 'USE pubs SELECT * FROM authors'
EXEC (@SqlString)
GO
August 24, 2001 at 9:06 pm
That solution will work, but will not get you the benefits of a compiled plan. I would look for an alternative to 'use' - in my opinion(!) this is far from a best practice. Maybe you could give us more details on your problem, see if we can offer other options for solving it?
Andy
December 8, 2002 at 4:37 pm
Usually you don't want to do this. Instead consider using a fully qualified data base name on your stored procs. If you need to build and send a query to a remote server, you might consider using OPENQUERY() to accomplish this.
December 9, 2002 at 8:02 am
You can also execute an SP_ stored procedure based in master on any database
myDatabase.dbo.sp_help fred
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply