sql - sp_executesql - 参数附近的语法不正确

Question

我正在使用带参数的查询来创建数据库用户和 SQL Server 登录名。我有下一个程序:

PROCEDURE [dbo].[regNewOfficial]
@name nvarchar(30),
@password nvarchar(30)
AS
DECLARE @sqlRequest NVARCHAR(1000);
SET @sqlRequest = 'CREATE LOGIN @nameP' +  
                    ' WITH PASSWORD = @passwordP' + 
                    ' , DEFAULT_DATABASE=[TreeBase]' +  
                    ' CREATE USER @nameP' + 
                    ' FOR LOGIN @nameP' +
                    ' ALTER ROLE official_role ADD MEMBER @nameP ;';
EXECUTE sp_executesql  @sqlRequest, N'@nameP nvarchar(30), @passwordP nvarchar(30)', @nameP = @name, @passwordP = @password;

我得到这个错误:

Incorrect syntax near "@nameP"

解决方案:连接字符串时需要使用QUOTENAME()

在没有 QUOTENAME() 的情况下使用参数的结果:

CREATE LOGIN @nameP WITH PASSWORD = @passwordP , DEFAULT_DATABASE=[TreeBase] CREATE USER @nameP FOR LOGIN @nameP ALTER ROLE official_role ADD MEMBER @nameP ;

使用QUOTENAME():

CREATE LOGIN [aaaaa] WITH PASSWORD = 'bbbbb' , DEFAULT_DATABASE=[TreeBase]; CREATE USER [aaaaa] FOR LOGIN [aaaaa]; ALTER ROLE official_role ADD MEMBER [aaaaa];

Answer 1

CREATE LOGIN 需要文字字符串才能工作；你不能给它传递变量。因此，您需要以不同的方式执行动态 SQL。

ALTER PROCEDURE [dbo].[regNewOfficial]
    @name nvarchar(30),
    @password nvarchar(30)
AS

    DECLARE @sqlRequest NVARCHAR(1000);
    SET @sqlRequest = N'CREATE LOGIN ' + QUOTENAME(@name) +
                      N' WITH PASSWORD = ' + QUOTENAME(@password,'''') +
                      N' , DEFAULT_DATABASE=[TreeBase]' + N';' + NCHAR(10) +  
                      N' CREATE USER ' + QUOTENAME(@name) +
                      N' FOR LOGIN ' + QUOTENAME(@name) + N';' + NCHAR(10) + 
                      N' ALTER ROLE official_role ADD MEMBER ' + QUOTENAME(@name) + N';';
    PRINT @sqlRequest; --This is your friend for debugging
    EXECUTE sp_executesql @sqlRequest;
GO