Conversion failed when converting the varchar value 'dynamic sql query' to data type int

1
votes

I have a stored procedure for checking if row exist. I use this with ADO.NET for a dynamic query instead of having similar queries for all my tables.

CREATE PROCEDURE [dbo].[Row_Exist]    
    @TableName VARCHAR(50),
    @ColumnName VARCHAR(50),
    @RowId INT
AS 
BEGIN 
    DECLARE @sqlQuery NVARCHAR(MAX) = ''

    SET @sqlQuery = 'SELECT COUNT(1) as count FROM  ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @RowId

    EXEC(@sqlQuery)
END

If I execute this with a valid rowid, tablename, and columnname, I get the following error

Conversion failed when converting the varchar value SELECT COUNT(1) as count FROM Users WHERE UserID = to data type int.

2
sqlsql-servertsql
That is lucky for you. The reason it fails is because @RowID is an int and you don't cast it to a string datatype. As such it attempts to convert all these elements to an int and fails. The reason I say you are lucky is because this code is wide open to sql injection. See this link. bobby-tables.com But...why do you need a procedure to count rows in all your tables anyway? Seems like something is off here. - Sean Lange
Well thank you for the heads up about sql injection, I would consider that. but I am only give this procedure data from inside my code, I am not excepting any data from outside such as a form, its just that I feel instead of having 10 queries to check if row exist in different tables, I would rather have only one procedure - Saad A
@SaadA it's much easier to debug code when calling Stored Procedures named dbo.DoesUserExist, dbo.DoesCustomerExist, etc. than debugging code that goes to a generic function that builds strings of SQL. More numerous, concise SPs don't cost more rent. - HardCode
When checking for the existence of one or more rows it is more efficient to use EXISTS rather than getting an exact COUNT and then just checking if it is greater than zero. - HABO

2 Answers

1
votes

This is because you try to concate int to varchar. Please try the below sql

CREATE PROCEDURE [dbo].[Row_Exist]    
    @TableName varchar(50),
    @ColumnName varchar(50),
    @RowId int
AS 
BEGIN 
 DECLARE @sqlQuery NVARCHAR(MAX) = ''

  SET @sqlQuery = 'SELECT COUNT(1) as count FROM  ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + CAST(@RowId AS VARCHAR(20))

 EXEC(@sqlQuery)
END
0
votes

Use parameters where you can. I tend to use REPLACE() to fill in the other values:

DECLARE @sqlQuery NVARCHAR(MAX) = '
SELECT COUNT(1) as count
FROM @TableName
WHERE @ColumnName = @RowId
';
SET @sqlQuery = REPLACE(REPLACE(@sqlQuery, '@TableName', quotename(@TableName)), '@ColumnName', quotename(@ColumnName));

EXEC sp_executesql @sqlQuery, N'@RowId int', @RowId=@RowId;