代码之家 › 专栏 › 技术社区 › Kate Fernando

存储过程-Msg 102,15级,状态1,第3行“”附近的语法不正确

dynamic-sql stored-procedures tsql sql-server

0

Kate Fernando · 技术社区 · 8 年前

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

下面是我如何尝试执行它。

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

Msg 102,15级,状态1,第3行

谢谢

2 回复 | 直到 8 年前

1

3

Lukasz Szozda 8 年前

首先请使用参数绑定:

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

其次,你应该使用 QUOTENAME SYSNAME .

The Curse and Blessings of Dynamic SQL

2

1

Tab Alleman 8 年前

添加转义单引号,使动态SQL字符串有效:

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