Conversion failed when converting the varchar value ' AND ID =' to data type int

Question

I was looking how I can parameterize table names and so I found dynamic sql queries. I finally got the proc saved, but when I execute it errors out with "Conversion failed when converting the varchar value ' AND ID =' to data type int." I have no idea what is going wrong when I try to execute this stored proc. I feel like it must be a typo but I cannot tell. The stored pro.

EDITED to incorporate the suggestion below. Still no luck unless I am doing this wrong.

-- =======================================================
-- Create Stored Procedure Template for Azure SQL Database
-- =======================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetFormFieldCDC]
(
    @formfieldId INT,
    @C___operation INT,
    @C___start_lsn binary(10),
    @tablename NVarchar(255)

)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @tablename

    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'SELECT *  FROM ' + @ActualTableName + ' WHERE __$start_lsn =' + CONVERT(NVARCHAR(255),@C___start_lsn) + ' AND __$operation =' +  @C___operation  + ' AND ID =' + @formfieldId + ';'

    -- Insert statements for procedure here
    EXEC sp_executesql @SQL, N' @formfieldId INT,
    @C___operation INT,
    @C___start_lsn binary(10),
    @tablename NVarchar(255)', @formfieldId ,
    @C___operation,
    @C___start_lsn,
    @tablename
END
GO

Use parameters with `sp_executesql` and this will not happen. — Gordon Linoff, Aug 18 '21 at 21:57
Also, properly quote you dynamic object name(s) with `QUOTENAME`; otherwise this is a huge security flaw. — Thom A, Aug 18 '21 at 21:59
"I was looking how I can parameterize table names and so I found dynamic sql queries." Now you have two problems. — Sergey Kalinichenko, Aug 18 '21 at 22:01
Finally, don't use `sp_` as a prefix, it is reserved, by Microsoft, for **S**pecial **P**rocedures. Using it comes at a performance coat and could result in your procedure simply not working one day. — Thom A, Aug 18 '21 at 22:03

score 1 · Accepted Answer · answered Aug 18 '21 at 23:24

You should pass the parameters all the way through to sp_executesql. Do not inject them, unless they are object names, in which case you need QUOTENAME.

You can also use OBJECT_ID to check for table existence, and throw an exception if there is no such object.

CREATE OR ALTER PROCEDURE [dbo].[GetFormFieldCDC]
(
    @formfieldId INT,
    @C___operation INT,
    @C___start_lsn binary(10),
    @schemaname sysname,
    @tablename sysname
)
AS

    SET NOCOUNT ON;

    IF (OBJECT_ID(QUOTENAME(@schemaname) + '.' + QUOTENAME(@tablename)) IS NULL)
        THROW 50000, 'Table not found', 0;

    DECLARE @sql AS NVARCHAR(MAX) = '
SELECT *
FROM ' + QUOTENAME(@schemaname) + '.' + QUOTENAME(@tablename) + '
WHERE __$start_lsn = @C___start_lsn
  AND __$operation = @C___operation
  AND ID = @formfieldId;
';

    EXEC sp_executesql @SQL,
    N' @formfieldId INT,
       @C___operation INT,
       @C___start_lsn binary(10)',
    @formfieldId = @formfieldId,
    @C___operation = @C___operation,
    @C___start_lsn = @C___start_lsn;

GO

That's not possible, this code will not produce the error `Conversion failed when converting the varchar value ' AND ID =' to data type int` because there is no implicit conversion happening here. What *actual* error message are you getting? — Charlieface, Aug 19 '21 at 12:50

Conversion failed when converting the varchar value ' AND ID =' to data type int

1 Answers1