CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdynamic-sql
Kate Fernando
Kate Fernando

Reputation: 381

Stored procedure - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ''

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.

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'.

Thanks.

Upvotes: 0

Views: 3744

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

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.

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

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31775

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

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

Upvotes: 1

Related Questions