在存储过程中声明参数时出错

I am trying to create a stored procedure that utilizes a variable number of parameters. As I am pretty new to writing stored procedures and TSQL in general, I decided to try and write it with only one parameter. However, I keep getting an error stating "Must declare scalar variable @FirstName" when I try to execute it. Right now, I am trying to store the SQL statement in another variable, @sql. My procedure looks like this:

我正在尝试创建一个使用可变数量参数的存储过程。由于我对编写存储过程和TSQL一般都很陌生，所以我决定尝试用一个参数编写它。但是，当我尝试执行它时，我不断收到一条错误，指出“必须声明标量变量@FirstName”。现在，我试图将SQL语句存储在另一个变量@sql中。我的程序看起来像这样：

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = @FirstName'
    EXEC (@sql)
END

I've looked elsewhere and tried EXEC sp_execute @sql which didn't work. Strangely, what does work is when I don't declare the @sql variable and instead just write my query normally. Since that is the case, I'm assuming there is some error in my usage of the SET and EXEC functions. I'm also not 100% sure that I'm using BEGIN and END properly. The way I understood it is that BEGIN and END separate SQL statements into logical blocks, and thus are more commonly used when IF comes into play. Could anyone tell me what exactly is going on with my parameter here? It's really confusing me as to why SQL Server thinks it's not declared.

我在其他地方看过并尝试过EXEC sp_execute @sql，但是没有用。奇怪的是，当我没有声明@sql变量而只是正常编写我的查询时，工作是什么。既然如此，我假设我使用SET和EXEC函数时出现了一些错误。我也不是100％确定我正在使用BEGIN和END。我理解它的方式是BEGIN和END将SQL语句分成逻辑块，因此在IF发挥作用时更常用。谁能告诉我这里的参数到底发生了什么？对于SQL Server认为它未被声明的原因，我真的很困惑。

6 个解决方案

#1

The variable parameter needs to be outside the quotes.

变量参数需要在引号之外。

SET @sql = N'SELECT e.* from Employee e
             WHERE e.FirstName = ''' + @FirstName + ''''

Or, better yet, run it without any dynamic SQL.

或者，更好的是，在没有任何动态SQL的情况下运行它。

SELECT e.* 
    from Employee e
    WHERE e.FirstName = @FirstName

#2

Because

因为

'Select  ... @FirstName'

is not the same as

是不一样的

Select ... @FirstName

One is a string and the other is a SQL Query

一个是字符串，另一个是SQL查询

What you should do instead is

你应该做的是

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = ''' + @FirstName + ''''
    EXEC (@sql)
END

#3

When you execute dynamic sql, you are switching contexts and variables don't move between contexts. Once you declare the SQL statement as a string, everythign must be provided to that string in order for it to recognize it.

当您执行动态sql时，您正在切换上下文，并且变量不会在上下文之间移动。将SQL语句声明为字符串后，必须将everythign提供给该字符串，以便识别它。

Obviously, you don't need dynamic SQL in this case, but once method of doing it is like so:

显然，在这种情况下你不需要动态SQL，但是一旦这样做的方法是这样的：

    ALTER PROCEDURE [dbo].[GetEmployeeByParameters] 
  (@FirstName varchar(50))      
AS
      BEGIN
          DECLARE @sql AS NVARCHAR(4000)
       SET @sql = 'SELECT e.* from Employee e
                  WHERE e.FirstName = @FirstName'
       EXEC sp_executeSQL @sql, N'@Firstname varchar(50)', @FirstName
   END

sp_executeSQL allows you to declare internal parameters (the second clause), and supply them with values (the last clause).

sp_executeSQL允许您声明内部参数（第二个子句），并为它们提供值（最后一个子句）。

#4

You need to change your query to the following as the @Firstname variable is not in scope:

您需要将查询更改为以下内容，因为@Firstname变量不在范围内：

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    DECLARE @sql AS NVARCHAR(4000)
    SET @sql = 'SELECT e.* from Employee e
               WHERE e.FirstName = ''' + @FirstName + ''''
    EXEC (@sql)
END

#5

As this is a "search" type query your doing with a variable number of params, you need to build the string up bit by bit -- you're on the right lines that it needs to be dynamic, but you also need to avoid SQL injection attacks (google "Little Bobby Tables"). Thus you need to use a parameterised dynamic SQL statement:

由于这是一个“搜索”类型查询，你使用可变数量的参数进行操作，你需要逐步构建字符串 - 你在正确的线上它需要是动态的，但你还需要避免SQL注入攻击（谷歌“小鲍比表”）。因此，您需要使用参数化动态SQL语句：

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
    @FirstName VARCHAR(50)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(4000)

    SET @sql = 'SELECT e.* FROM Employee e WHERE 1 = 1'
    IF @FirstName IS NOT NULL
    BEGIN
        SET @sql = @sql + ' AND FirstName = @pFirstName'
    END
    -- More IF statements, building up the query

    EXEC sp_ExecuteSQL @sql, N'@pFirstName VARCHAR(50)', @FirstName

The second and third params map the @FirstName parameter to the query's "internal" parameters (which I normally prefix with a 'p' or 'param' just to differentiate them from the stored procedure's own parameters).

第二个和第三个参数将@FirstName参数映射到查询的“内部”参数（我通常用“p”或“param”作为前缀，以便将它们与存储过程自己的参数区分开来）。

You extend the sp_Exceute as appropriate each time you add a new parameter to search by, so you might end up doing:

每次添加要搜索的新参数时，都会根据需要扩展sp_Exceute，因此最终可能会执行以下操作：

    EXEC sp_ExecuteSQL @sql,' N'
              @pFirstName   VARCHAR(50),
              @pSurName     VARCHAR(50),
              @pDateOfBirth DATETIME', @FirstName, @Surname, @DateOfBirth

#6

Use this body, without "dynamic" query

使用此正文，无需“动态”查询

ALTER PROCEDURE [dbo].[GetEmployeeByParameters]
(@FirstName varchar(50))

AS

BEGIN

    SELECT e.* from Employee e
               WHERE e.FirstName = @FirstName
END

要么

using dynamic query do not include variables itselves into the script - do concatenate them with the script and do not forget to properly quote them.

使用动态查询不将变量本身包含在脚本中 - 将它们与脚本连接起来，不要忘记正确引用它们。

#1