我该怎么做: USE @databaseName [英] How can I do something like: USE @databaseName

问题描述

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

没用.如何制作?

推荐答案

如果你想像那样动态地做，你就必须使用动态 SQL.意味着您想在该数据库的上下文中执行的任何内容，您也需要包含在动态 SQL 语句中.

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.

即假设您要列出 MainDB 中的所有表:

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

这不起作用，因为 USE 语句在不同的上下文中 - 一旦 EXECUTE 运行，以下 SELECT 将不会在相同的上下文中运行，因此不会在 MainDb 中运行(除非连接是已经设置为 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

所以你需要这样做:

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

当然，您需要非常小心 SQL 注入，为此我将您指向 Barry 回答中的链接.

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

为了防止 SQL 注入，您还可以使用 QUOTENAME()函数，它将参数包装在方括号中:

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

这篇关于我该怎么做: USE @databaseName的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！