CODEWITHSUNDEEP
sqlsql-servert-sql
user5011655
user5011655

Reputation:

Store a database name in variable & then using it dynamically

I have a Table in my Database Which have name of all the Database of my Server

Table Look like 

   create   Table  #db_name_list(Did INT IDENTITY(1,1), DNAME NVARCHAR(100))
    INSERT INTO #db_name_list
    SELECT 'db_One ' UNION ALL
    SELECT 'db_Two' UNION ALL
    SELECT 'db_Three' UNION ALL
    SELECT 'db_four' UNION ALL
    SELECT 'db_five'

select * from #db_name_list

I have so many SP in my Database..Which uses multiple table and Join Them..

At Present I am using the SQL code like

Select Column from db_One..Table1
Left outer join db_two..Table2
on ....some Condition ....

REQUIREMENT But I do not want to HARDCODE the DATABASE Name ..

I want store DataBase name in Variable and use that . Reason :: I want to restore same Database with Different name and want to Run those SP..At Present we Cant Do ,Because I have used db_One..Table1 or db_two..Table2

I want some thing like ...

/SAMPLE SP/

CREATE PROCEDURE LOAD_DATA
AS
BEGIN

  DECLARE @dbname nvarchar(500)
  set @dbname=(  SELECT DNAME FROM #db_name_list WHERE Did=1)
 set @dbname2=(  SELECT DNAME FROM #db_name_list WHERE Did=2)
  PRINT @DBNAME
  SELECT * FROM   @dbname..table1 
  /* or */
  SELECT * FROM   @dbname2.dbo.table1

END

i.e using Variable Instead of Database name .. But it thow error

"Incorrect syntax near '.'."

P.S This was posted by some else on msdn but the answer there was not clear & I had the same kind of doubt. So please help

Upvotes: 0

Views: 3870

Answers (2)

LunarSage
LunarSage

Reputation: 285

There seem to be a couple of options for you depending on your circumstances.

1. Simple - Generalise your procedures

Simply take out the database references in your stored procedure, as there is no need to have an explicit reference to the database if it is running against the database it is stored in. Your select queries will look like:

SELECT * from schema.table WHERE x = y

Rather than 

SELECT * from database.schema.table WHERE x = y

Then just create the stored procedure in the new database and away you go. Simply connect to the new database and run the SP. This method would also allow you to promote the procedure to being a system stored procedure, which would mean they were automatically available in every database without having to run CREATE beforehand. For more details, see this article.

2. Moderate - Dynamic SQL

Change your stored procedure to take a database name as a parameter, such as this example:

CREATE PROCEDURE example (@DatabaseName VARCHAR(200))
AS
BEGIN 
    DECLARE @SQL VARCHAR(MAX) = 'SELECT * FROM ['+@DatabaseName+'].schema.table WHERE x = y'
    EXEC (@SQL)
END

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

You can't use a variable like this in a static sql query. You have to use the variable in dynamic sql instead, in order to build the query you want to execute, like:

DECLARE @sql nvarchar(500) = 'SELECT * FROM ' + @dbname + '.dbo.mytable'

EXEC(@sql);

Upvotes: 1

Related Questions