Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I do something like: USE @databaseName

 DECLARE @DatabaseName NVARCHAR(max); SET @DatabaseName = 'MainDb'  USE @DatabaseName 

Wouldn't work. How to make it?

like image 808
iLemming Avatar asked Sep 24 '10 15:09

iLemming


People also ask

How can use dynamic database in SQL Server?

If the goal is to execute some static SQL inside the chosen database, maybe you should consider storing that static SQL in a stored procedure in each database, and calling it dynamically like this: DECLARE @db sysname = N'db1'; DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.

How do I switch between SQL databases?

In Azure SQL Database, the database parameter can only refer to the current database. If a database other than the current database is provided, the USE statement does not switch between databases, and error code 40508 is returned. To change databases, you must directly connect to the database.

What is Dynamic SQL example?

For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime. In past releases of Oracle, the only way to implement dynamic SQL in a PL/SQL application was by using the DBMS_SQL package.


1 Answers

You'd have to use dynamic SQL if you want to do it dynamically like that. Would mean anything you want to execute under the context of that DB, you'd need to include in the dynamic SQL statement too.

i.e. assume you want to list all the tables in MainDB:

This won't work, as the USE statement is in a different context - once that EXECUTE has run, the following SELECT will NOT be running in that same context and so won't be running in MainDb (unless the connection was already set to MainDb)

DECLARE @DatabaseName NVARCHAR(MAX) SET @DatabaseName = 'MainDb' EXECUTE('USE ' + @DatabaseName) -- SQL injection risk! SELECT name FROM sys.tables 

So you'd need to do:

DECLARE @DatabaseName NVARCHAR(MAX) SET @DatabaseName = 'MainDb' EXECUTE('USE ' + @DatabaseName + ';SELECT name FROM sys.tables') -- SQL injection risk! 

Of course, you need to be very careful with SQL injection, for which I point you to the link in Barry's answer.

To prevent SQL Injection, you could also use QUOTENAME() function, it wraps parameter in square brackets:

DECLARE @DatabaseName sysname = 'MainDb'     , @SQL NVARCHAR(MAX);  SET @SQL = N'USE ' + QUOTENAME(@DatabaseName);  PRINT(@SQL); -- USE [MainDb]  EXECUTE(@SQL); 
like image 172
AdaTheDev Avatar answered Oct 20 '22 04:10

AdaTheDev