Conversion failed when converting the varchar value ' and f.FUNCTION_ID= CAST(' to data type int

Question

I am trying to make the procedure where I can get the data from MS SQL. Here I am getting the error

Msg 245, Level 16, State 1, Procedure GET_FUNCTIONS, Line 15 [Batch Start Line 33] Conversion failed when converting the varchar value ' and f.FUNCTION_ID in '' to data type int.

    CREATE procedure [dbo].[GET_FUNCTIONS]
    @Function_Id int,
    @EntityId int,
    @OrderBy varchar(10)
    as
    begin
    declare @Sql nvarchar(max)
    if(@Function_Id=0 AND @EntityId=0)
    set @Sql = 'select                                                        
    f.Function_Code,f.FUNCTION_ID,f.EntityId,be.ENTITY_SHORT_NAME,f.FUNCTION_NAME,
    f.FUNCTION_DESC,f.CREATED_DATE,f.MODIFIED_DATE from FUNCTIONS f 
    inner join BusinessEntity be on f.EntityId=be.ID
    WHERE f.ACTIVE=1 '

    if(@Function_Id!=0)
    set @Sql += ' and f.FUNCTION_ID in ''' + @Function_Id + ''''
    if(@EntityId!=0)
    set @Sql += ' and f.EntityId = cast('+convert(varchar(12) ,@EntityId)+') '

    set @Sql += ' order by FUNCTION_ID '+ @OrderBy
    print @Sql
    end
    GO

Fix the injection issue, fix the problem. **NEVER** inject parameters into a dynamic statement, parametrise it, and quote your dynamic objects *properly*. [Dos and Don'ts of Dynamic SQL](https://www.sqlservercentral.com/articles/dos-and-donts-of-dynamic-sql) — Thom A, Jan 03 '20 at 13:30
What is _@function_id_? An int. What happens when you concatenate an int with varchar - which are 2 expressions with different datatypes? One of them is converted to the other based on well defined rules. Int has higher precedence, hence the error. Fix your concatenation. — SMor, Jan 03 '20 at 13:33

score 1 · Accepted Answer · answered Jan 07 '20 at 10:15

1

Thanks everyone for your answers. I have got the answer by my own.

I have removed if(@Function_Id=0 AND @EntityId=0) and changed the data type of @Function_Id and @EntityId to varchar(100), so solved all errors.

answered Jan 07 '20 at 10:15

ttr

11
7

Thom A · Answer 2 · 2020-01-04T15:52:12.437

I'm, making a few guesses here, which I add some comments about, however, this should be what you are after. I firstly ensure that I parametrise the values, but also validate and quote the value of @OrderBy:

CREATE PROCEDURE [dbo].[GET_FUNCTIONS] @Function_Id int =NULL, --Instead of passing 0as "select all" use NULL
                                       @EntityId int = NULL, --Instead of passing 0as "select all" use NULL
                                       @OrderBy nvarchar(4) --guessing this is mean to be ASC or DESC?
AS
BEGIN

    IF @OrderBy NOT IN (N'ASC','DESC')
        --Error is not ASC or DESC
        THROW 51473, N'@OrderBy can only have a value of ''ASC'' or ''DESC''.',16; --51473 was a random error number; you can use one that is appropriate for your environment.

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'SELECT f.Function_Code,' + + @CRLF +
               N'       f.FUNCTION_ID,' + + @CRLF +
               N'       f.EntityId,' + + @CRLF +
               N'       be.ENTITY_SHORT_NAME,' + + @CRLF +
               N'       f.FUNCTION_NAME,' + + @CRLF +
               N'       f.FUNCTION_DESC,' + + @CRLF +
               N'       f.CREATED_DATE,' + + @CRLF +
               N'       f.MODIFIED_DATE' + + @CRLF +
               N'FROM FUNCTIONS f' + + @CRLF +
               N'     INNER JOIN BusinessEntity be ON f.EntityId = be.ID' + + @CRLF +
               N'WHERE f.ACTIVE = 1';

    IF (@Function_Id IS NOT NULL)
        SET @SQL = @SQL + @CRLF + N'  AND f.FUNCTION_ID = @Function_Id'

    IF (@EntityId IS NOT NULL)
        SET @SQL = @SQL + @CRLF + N'  AND f.EntityId = @EntityId'

    SET @SQL = @SQL + @CRLF +
               N'ORDER BY F.FUNCTION_ID ' + QUOTENAME(@OrderBy) + N';'

    --PRINT @SQL; --Uncomment for debugging.
    EXEC sp_executesql @SQL, N'@Function_Id int, @EntityId int', @Function_Id, @EntityId;
END;
GO

Good Suggestions but no help as it is not showing the result if I am executing the procedure. — ttr, Jan 04 '20 at 15:46
then there are no results in the dataset, @ttr, the the resulting value opf `@SQL` is `NULL`. Use the inbuilt debugger i gave you — Thom A, Jan 04 '20 at 15:47
I have data in table but after passing the values it is not showing the result. — ttr, Jan 04 '20 at 15:48
The debugger is showing the exact query which I have to run but no values in `@Function_Id` and `@EntityId`. — ttr, Jan 04 '20 at 15:50
No, it won't, @ttr, but *you* know what you've passed for `@Function_ID` and `@Entity_ID` — Thom A, Jan 04 '20 at 15:52
I would suggest you to try from your end and you will get the answer of what I am saying. — ttr, Jan 04 '20 at 16:15
I can't, I don't have access to your instance, @ttr and you provided no sample data or objects. — Thom A, Jan 04 '20 at 16:17

Conversion failed when converting the varchar value ' and f.FUNCTION_ID= CAST(' to data type int

2 Answers2