How can I do something like: USE @databaseName

by

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);

More Related Contents:

  1. SQL Server – error when creating stored procedure
  2. How can I remove duplicate rows?
  3. SQL Server: Examples of PIVOTing String data
  4. Check if table exists in SQL Server
  5. Using T-SQL, return nth delimited element from a string
  6. What is the use of GO in SQL Server Management Studio & Transact SQL?
  7. SELECT DISTINCT on one column
  8. Using a if condition in an insert SQL Server
  9. Altering a column: null to not null
  10. Maximum size of a varchar(max) variable
  11. best way to convert and validate a date string
  12. Confused about UPDLOCK, HOLDLOCK
  13. TSQL Email Validation (without regex)
  14. Dynamic SQL results into temp table in SQL Stored procedure
  15. How do I find a stored procedure containing ?
  16. TSQL: Create a view that accesses multiple databases
  17. Sequence as default value for a column
  18. Aggregate bitwise-OR in a subquery
  19. How do I flush the PRINT buffer in TSQL?
  20. OR Operator Short-circuit in SQL Server
  21. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. How to change default language for SQL Server?
  24. T-SQL incrementing counter based on change in a column value
  25. T-SQL XOR Operator
  26. Cannot bulk load. Operating system error code 5 (Access is denied.)
  27. Does query plan optimizer works well with joined/filtered table-valued functions?
  28. How to wait for 2 seconds?
  29. What is the meaning of “#” in front of a table name in TSQL?
  30. T-SQL for finding Redundant Indexes

Leave a Comment