CODEWITHSUNDEEP
sql-serverstored-procedures
Cato Johnston
Cato Johnston

Reputation: 45679

Is it possible to pass db name as a parameter

I have about 100 sites built in a cms, each with its own database. Each database has the same tables.

My stored procedure needs to select all of the pages in a database given a sitename.

Below I am trying to pass the database name as a parameter, but it doesn't seem to work.

...
@site nvarchar(250)

AS

SELECT *
FROM @site..cmsDocument
WHERE published = 1

Is there some other way to do this?

Upvotes: 2

Views: 6981

Answers (5)

Travis Miller
Travis Miller

Reputation: 23

If the query / procedure is being executed from a .NET application, you can also programmatically change the database of the connection object (VB.NET):

Using da As New SqlDataAdapter("SELECT * FROM cmsDocument WHERE published = 1", GetDatabaseConnection(ServerName))
     da.SelectCommand.Connection.ChangeDatabase("Foobar")
End Using

Upvotes: 0

Diego
Diego

Reputation:

You can use the undocumented stored procedure sp_MSforeachdb: sp_MSforeachdb 'SELECT * FROM ?..table_name_here'

Upvotes: 0

Guffa
Guffa

Reputation: 700362

You can specify the database in the connection string:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300559

SELECT @dbname = quotename(dbname)
SELECT @sql = ' SELECT ... FROM ' + @dbname + '.dbo.tablename WHERE ...'
EXEC sp_executesql @sql, @params, ...

Refs:

sp_executesql (Transact-SQL)

The Curse and Blessings of Dynamic SQL

Upvotes: 3

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798686

Almost no DBMSes allow you to use a parameter in this manner. Build a dynamic query via concatenation and execute it.

Upvotes: 1

Related Questions