如何将参数值传递给存储过程，而不是SQL Server中的文本字符 [英] How to pass parameter value to a stored procedure rather than text lietral in SQL Server

问题描述

从最近的帖子中进行下一步（下）

• 
  
  

  在调试时，我发现它没有使用参数的值，而是文字。

  
  
  

  如何在此处传递参数值以使用值相应地创建登录名，用户&

  
  
  

  谢谢！

  解决方案

  这里的问题是你重新尝试使用变量替换文字。这不是SQL的工作方式（它不是脚本语言）。例如，采用以下语句：

   创建用户[@userName] FOR登录[@loginName] 
    

  这将创建一个名为 @userName的 USER 链接到称为 @loginName 的 LOGIN ，不是一个 USER ，其值的名称为 @userName $ c> LOGIN ，其名称为 @loginName 。

  
  
  

  这样的事情，您需要使用动态SQL并安全地 注入参数。

    ALTER PROCEDURE dbo.CreateUser @loginName sysname，-更改了整个数据类型以更正对象
   @userName sysname，
   @schemaName sysname 
   AS 
   BEGIN 
   
  开启NOCOUNT； 
  如果不存在（从[sys]中选择[name] 
  。[database_principals] 
   WHERE [type] = N'S'
   AND [name] = @loginName）
  开始
  声明@SQL nvarchar（MAX），
   @CRLF nchar（2）= NCHAR（13）+ NCHAR（10）; 
   
   SET @SQL = N'CREATE LOGIN'+ QUOTENAME（@loginName）+ @CRLF + 
   N'WITH PASSWORD = N''123''，'+ @CRLF +-我强烈建议您选择一个更好的密码... 
   N'DEFAULT_DATABASE = [测试]，'+ @CRLF + 
   N'CHECK_EXPIRATION = OFF，'+ @CRLF + 
   N'CHECK_POLICY = OFF;'+ @CRLF + 
   N'CREATE USER'+ QUOTENAME（@userName）+ N'FOR LOGIN'+ QUOTENAME（@loginName）+ N';'+ @CRLF + 
   N'GRANT选择，插入，更新，在模式上删除：: [dbo]到'+ QUOTENAME（@userName）+ N';'+ @CRLF + 
   N'GRANT SELECT，'+ @CRLF + 
   N 'INSERT，'+ @CRLF + 
   N'UPDATE，'+ @CRLF + 
   N'DELETE'+ @CRLF + 
   N'ON SCHEMA ::'+ QUOTENAME（@schemaName） + @CRLF + 
   N'TO'+ QUOTENAME（@userName）+ N';'; 
   
   --PRINT @SQL; -您的调试朋友
   
   EXEC sys.sp_executesql @SQL; 
   END； 
   
   END； 
    

  
  

  Taking the next step from the recent post (below)

  • How to grant access to multiple schemas in one go in SQL Server

  I created this stored procedure:

  ALTER PROCEDURE dbo.CreateUser
      @loginName nvarchar(100),
      @userName nvarchar(100) ,
      @schemaName nvarchar(10)
  AS
      SET NOCOUNT ON;
  
      IF NOT EXISTS (SELECT [name] FROM [sys].[database_principals] 
                     WHERE [TYPE] = N'S' AND [name] = @loginName)
      BEGIN
          CREATE LOGIN [@loginName] WITH PASSWORD = N'123', DEFAULT_DATABASE = [test], 
                       CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
  
          CREATE USER [@userName] FOR login [@loginName]
  
          GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO [@userName]
          GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [@schemaName] TO [@userName]
  END
  

  I'm calling this stored procedure like this:

  DECLARE @return_value int
  
  EXEC    @return_value = [dbo].[CreateUser]
                          @loginName = N'testlogin1',
                          @userName = N'testUserLogin1',
                          @schemaName = N'itl'
  
  SELECT 'Return Value' = @return_value
  

  However, this throws an error:

  Upon debugging I see that its not taking the value of the parameters rather the literal.

  How do I pass parameter value here to use the values accordingly to create login, user & access?

  Thanks!

  解决方案

  The problem here is you're trying to use a variable to replace literal. That isn't how SQL works (it's not a Scripting Language). For example, take the statement below:

  CREATE USER [@userName] FOR login [@loginName]
  

  This will create a USER called @userName which is linked to the LOGIN called @loginName, not a USER with a name of the value of @userName for the LOGIN with the name of the value of @loginName.

  For things like this, you need to use dynamic SQL and safely inject your parameters.

  ALTER PROCEDURE dbo.CreateUser @loginName sysname, --Changed data type throughout to correct one for objects
                                 @userName sysname,
                                 @schemaName sysname
  AS
  BEGIN
  
      SET NOCOUNT ON;
      IF NOT EXISTS (SELECT [name]
                     FROM [sys].[database_principals]
                     WHERE [type] = N'S'
                       AND [name] = @loginName)
      BEGIN
          DECLARE @SQL nvarchar(MAX),
                  @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
  
          SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@loginName) + @CRLF +
                     N'    WITH PASSWORD = N''123'',' + @CRLF + --I HIGHLY recommend a better password choice...
                     N'         DEFAULT_DATABASE = [test],' + @CRLF +
                     N'         CHECK_EXPIRATION = OFF,' + @CRLF +
                     N'         CHECK_POLICY = OFF;' + @CRLF +
                     N'CREATE USER ' + QUOTENAME(@userName) + N' FOR LOGIN ' + QUOTENAME(@loginName) + N';' + @CRLF +
                     N'GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[dbo] TO ' + QUOTENAME(@userName) + N';' + @CRLF +
                     N'GRANT SELECT,' + @CRLF +
                     N'      INSERT,' + @CRLF +
                     N'      UPDATE,' + @CRLF +
                     N'      DELETE' + @CRLF +
                     N'    ON SCHEMA::' + QUOTENAME(@schemaName) + @CRLF +
                     N'    TO ' + QUOTENAME(@userName) + N';';
  
          --PRINT @SQL; --Your debugging friend
  
          EXEC sys.sp_executesql @SQL;
      END;
  
  END;
  

  这篇关于如何将参数值传递给存储过程，而不是SQL Server中的文本字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！