CODEWITHSUNDEEP
sqlsql-servert-sql
Saad A
Saad A

Reputation: 1147

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

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.

Upvotes: 1

Views: 3687

Answers (2)

Zafor
Zafor

Reputation: 353

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 0

Related Questions