Creating Logins with Dynamic Parameters: Overcoming the "@parameter as username" Obstacle

In the pursuit of creating custom Stored Procedures to manage tenant settings, Justin encountered a perplexing hurdle: the inability to utilize parameterized usernames in the CREATE LOGIN statement.尽管此任务的性质看似直接，但隐秘的SQL错误消息证明是令人不安的。

问题源于以下事实：创建登录期望具有字面用户名与参数化值相对。为了规避这一限制，贾斯汀可以采用动态的SQL技术。

动态SQL方法

In this code:

The DECLARE statement assigns the CREATE LOGIN statement to the variable @sql. Quoting the username and password values using the quotename function helps protect against SQL injection attacks.

DECLARE @sql nvarchar(max) = 'CREATE LOGIN '   quotename(@username)   ' WITH PASSWORD = '   quotename(@password, '''');
EXEC(@sql)

The EXEC statement executes the dynamic SQL stored in @sql.

By wrapping the CREATE LOGIN statement within EXEC, Justin can effectively pass parameterized values into the statement at runtime, resolving the "Incorrect syntax near '@username'"错误。
• cendusion
使用动态SQL在使用需要文字值而不是参数的SQL语句时提供了一个解决方案。通过接受这项技术，贾斯汀可以自信地在其存储过程中创建租户登录，使他有能力自动化租户管理过程并简化他的SaaS数据库管理。