如何使用 sp_executesql 找出用户是否存在于数据库中? [英] How to find out if a user exists in a db using sp_executesql?

问题描述

我正在尝试使用 sp_executesql 但似乎无法获得正确的语法.这是我正在尝试的内容:

I'm trying to use sp_executesql but can't seem to get the syntax right to do so. Here is what I'm trying out:

DECLARE @userName1 varchar(20)
DECLARE @userexists bit

SET @userName1 = 'testUser'

EXEC sp_executesql N'select @userexists = name FROM sys.database_principals WHERE name = ' + @userName1, N'@userexists bit output', @userexists output

PRINT @userexists

这怎么工作?感谢您的帮助.

How can this work? Thanks for your help.

推荐答案

试试这个

EXEC sp_executesql 
  N'select @userexists=1 FROM sys.database_principals WHERE name = @userName1',
  N'@userName1 varchar(20), @userexists bit output',
  @userName1,
  @userexists output

在这种情况下，您将获得 1 或空结果.

In this case you will get 1 or empty result.

所以，你可以试试这个:

So, you can try this :

 EXEC sp_executesql 
  N'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

这篇关于如何使用 sp_executesql 找出用户是否存在于数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！