>>>Hi
>>>
>>>I want to pass a database name into my stored procedure and then use that database for everything that the stored procedure does.
>>>
>>>Whats the syntax for that ?
>>>
>>>Thanks
>>
>>You must use so called Dynamic SQL.
>>BTW why you need this?
>>If you call Database SP it will be executed for that DB, no matter to which DB you are connected.
>
>Hi Boriss
>
>I wanted to have a stored procedure in one database that could optionally be run against several other databases so I could pass in the database as a parameter.
>
>
>So I can't do that ?
>
>I have to have the stored procedure in the database its to run on ?
>
>Thanks
You can, but as I said you should use so called dynamic SQL (EXEC or sp_executesql).
That can slow down the performance.
Something like that:
USE YourDB
CREATE PROCEDURE Test(
@db_name nvarchar(200) = NULL
)
AS
BEGIN
IF @db_name IS NULL
SET @db_name = DB_NAME()
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM '+@db_name+'.dbo.SomeTable'
EXEC sp_executesql @sql
END
EXEC Test
EXEC Test @db_name = 'SomeOtherDB'
This is a simple SP, no error handling is included :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.