Variable database name when executing stored procedure
-
I am schema qualifying a stored procedure when calling it such that if I have a database named 'MyDB1' and the stored procedure 'GetContacts' that belongs to 'dbo' schema, I can call it like: EXEC MyDB1.dbo.GetContacts However, I need to be able to dynamically call the stored procedure from different databases that that have the same stored procedure such that i can call them like: EXEC MyDB1.dbo.GetContacts EXEC MyDB2.dbo.GetContacts EXEC MyDB3.dbo.GetContacts Is there a way I can use a variable name in place of the database name such that I can call the stored procedure like: DECLARE @DBName NVARCHAR(20) SET @DBName = 'MyDB1' EXEC @DBName.dbo.GetContacts and so forth, ... Or, what options do I have?
-
I am schema qualifying a stored procedure when calling it such that if I have a database named 'MyDB1' and the stored procedure 'GetContacts' that belongs to 'dbo' schema, I can call it like: EXEC MyDB1.dbo.GetContacts However, I need to be able to dynamically call the stored procedure from different databases that that have the same stored procedure such that i can call them like: EXEC MyDB1.dbo.GetContacts EXEC MyDB2.dbo.GetContacts EXEC MyDB3.dbo.GetContacts Is there a way I can use a variable name in place of the database name such that I can call the stored procedure like: DECLARE @DBName NVARCHAR(20) SET @DBName = 'MyDB1' EXEC @DBName.dbo.GetContacts and so forth, ... Or, what options do I have?
Hi, you can use sp_executesql N'exec ' where is constructed as @DBName.dbo.GetContacts You can also pass parameters on this way. Take a look at http://msdn2.microsoft.com/en-us/library/ms188001.aspx Best regards, DLM@bypsoft.com
FREE cross database comparison and synchronization tools DBTYP.NET - see database differences, for free (SQL Server, MySQL, Oracle)