如何使用 sp_executesql 循环数据库 [英] how to loop dbs using sp_executesql

问题描述

跟进问题sp 执行代码

我想遍历存储在游标中的 dbs 名称列表，但我不知道在将 USE 命令集成到 sp execute 命令时有什么作用，因为我需要该语句来防止更改数据库.

I'd like to loop through a list of dbs names that are stored in a cursor, and i don't know what works in integrating a USE command to the sp execute command, since i need that statement to work against changing dbs.

我已经尝试了下面的代码，但是 sp_executesql 在@dbfullname 上返回一个语法错误.(在了解更多 sp_executesql 的定义之后，我想没有办法添加那个 USE 命令.那么还能怎么做?有什么指针吗?是否需要在批处理中完成?)

i've tried the code below but sp_executesql returns a syntax error on @dbfullname. (after understanding more the definition of sp_executesql, i'm thinking there is no way to add that USE command. So how else can this be done? any pointers? does it need to be done in a batch transaction?)

        declare @dbfullname varchar(60)
        declare @name varchar(50)
        DECLARE @userName1 varchar(20)
        DECLARE @userexists bit

        SET @userName1 = 'testUser'

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @dbfullname = N'MyDb' + @name

        EXEC sp_executesql  N'use @dbfullname; SET @userexists = CASE WHEN EXISTS(SELECT 1 
                                FROM  sys.database_principals
                                WHERE name = @userName1)
                                THEN 1 ELSE 0 
                                   END',
                            N'@userName1 varchar(20), @userExists bit output',
                            @userName1,
                            @userExists output

    END

推荐答案

USE 不接受变量或表达式，只接受静态数据库名称.

USE does not accept a variable or expression, only a static database name.

您可以在 EXEC 之前组合命令，例如:

You can assemble the command prior to the EXEC, e.g.:

set @dbfullname = N'MyDb' + @name;
declare @SQL as NVarChar(1000) = N'use ' + @dbfullname +
  N'; set @userExists = case when exists ...;';
exec SP_ExecuteSQL @SQL, @userName1, @userExists output;

请注意，如果 @dbfullname 中存在任何顽皮值的可能性，这会使您面临 SQL 注入攻击.

Note that if there is any possibility of a naughty value in @dbfullname that this exposes you to SQL injection attacks.

这篇关于如何使用 sp_executesql 循环数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！