Sql – Conversion failed when converting the varchar value to data type int – but I’m not converting anything

sqlsql server

I am trying to write a stored procedure that takes two parameters: table name and record ID.

It must return a record with a specified ID (@FormID) from the table with the specified name (@TableName).

I get this error:

Conversion failed when converting the varchar value 'SELECT * FROM [Form12_AuditLog] WHERE [FormID] = ' to data type int."

I can't really understand the issue because I'm not trying to convert anything to data type int.

The parameters passed to the SQL are: 

@FormID = 88
@TableName = Form12_AuditLog

SQL:

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[GetAuditLogByFormID]    Script Date: 20/12/2016 5:50:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAuditLogByFormID] 
    @TableName varchar(50),
    @FormID integer
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ActualTableName AS varchar(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 [FormID] = ' + @FormID  + ';'

    EXEC(@sql)
END

Best Answer

Try:

SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + CONVERT(NVARCHAR,@FormID)  + ';'

SQL is trying to convert all of the strings you're attempting to concatenate into INT values because @FormID is an INT and you're using the + operator - this is quite common when concatenating values which are a mixture of string and non-string types. You therefore need to explicitly tell SQL that the INT value should be treated as a string-type value for the purposes of the concatenation.

Related Topic