I need to use different databases in a SP

i am sending a database name to a SP , i need that the SP executes some
statements over that database, can anyone help me to fin how can i do this?"Dario Morales" <dmorales@.trfe.org.mx> wrote in message
news:uPBmdFsaFHA.2980@.TK2MSFTNGP10.phx.gbl...
> i am sending a database name to a SP , i need that the SP executes some
> statements over that database, can anyone help me to fin how can i do
this?
>
You could use Dynamic SQL.
CREATE PROC Foo
@.dbname sysname
AS
-- Does some work using Dynamic SQL
BEGIN
DECLARE @.sql varchar(4000)
SET @.sql = 'SELECT col1, col2, col3 FROM ' + @.dbname + '.dbo.Table1'
EXECUTE (@.sql)
END
This is just a start.
Rick Sawtell|||You can use 3-part naming directly in a stored procedure, as anywhere else:
SELECT *
FROM database.owner.object ...
If you are passing the database name as a parameter:
DECLARE @.storedproc
SET @.storedproc = @.db + '..some_sp'
EXEC @.storedproc @.param1, @.param2...
And of course you can use Dynamic SQL where you can use EXEC or
sp_ExecuteSQL to execute a dynamically built string within a stored
procedure. But make sure you have sufficient precautions taken against
potential security risks and injection.
Anith