存储过程 - 消息102，级别15，状态1，行3'''附近的语法不正确

I am new to stored procedures and I want to get the id of some row by using that row column 'name'. Following is the stored procedure that I used.

我是存储过程的新手，我希望通过使用行列'name'来获取某行的id。以下是我使用的存储过程。

ALTER PROCEDURE [dbo].[SP_Get_SHGO_ID] 
    -- Add the parameters for the stored procedure here
     @TableName VARCHAR(50)
    ,@SHGO_Name VARCHAR(100)
    ,@Ret int OUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Query AS NVARCHAR(MAX)
    DECLARE @SHGO_ID AS INT

    SET @Query = N'SELECT @x=ID FROM '+@TableName+' WHERE SHGO_Name=' +@SHGO_Name
    EXECUTE  sp_executesql  @Query,N'@x int out', @SHGO_ID out

    SET @Ret = @SHGO_ID
END

Below is how I try to execute it.

以下是我尝试执行它的方法。

USE [UL_SLHEV]
GO

DECLARE @return_value int,
        @Ret int

EXEC    @return_value = [dbo].[SP_Get_SHGO_ID]
        @TableName = N'dbo.SHGO',
        @SHGO_Name = N'AITKEN SPENCE',
        @Ret = @Ret OUTPUT

SELECT  @Ret as N'@Ret'

SELECT  'Return Value' = @return_value

GO

But I am getting the following error. Can anyone help me with this?

但是我收到以下错误。谁能帮我这个？

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SPENCE'.

消息102，级别15，状态1，行3'SPENCE'附近的语法不正确。

Thanks.

谢谢。

2 个解决方案

#1

Add escaped single quotes so that your dynamic SQL string is valid:

添加转义的单引号，以便您的动态SQL字符串有效：

SET @Query = N'SELECT @x=ID FROM '+@TableName+' WHERE SHGO_Name=''' +@SHGO_Name + ''''

#2

First of all please use parameter binding:

首先请使用参数绑定：

ALTER PROCEDURE [dbo].[SP_Get_SHGO_ID] 
    -- Add the parameters for the stored procedure here
     @TableName VARCHAR(50)  -- should be SYSNAME
    --,@schemaName SYSNAME    -- schema and table should be separated
    ,@SHGO_Name VARCHAR(100)
    ,@Ret int OUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Query AS NVARCHAR(MAX)
    DECLARE @SHGO_ID AS INT

    SET @Query = N'SELECT @x=ID FROM '+@TableName+' WHERE SHGO_Name=@SHGO_Name'
    EXECUTE  sp_executesql  
        @Query
       ,N'@x int out, @SHGO_Name VARCHAR(100)'
       ,@SHGO_ID out
       ,@SHGO_Name;

    SET @Ret = @SHGO_ID
END

Second you should use QUOTENAME to secure table name, third indetifiers are type of SYSNAME.

其次你应该使用QUOTENAME保护表名，第三个保密者是SYSNAME的类型。

Before you move on, please read The Curse and Blessings of Dynamic SQL

在继续之前，请阅读动态SQL的诅咒和祝福

#1