CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user3378165
user3378165

Reputation: 6896

Conversion failed when converting the varchar value '%' to data type int Error

I have this query that is based on a @Amount parameter

Amount varchar(20)= NULL

SELECT .....
    Format(T1.Amount, 'c', 'en-us') as Amount1, 
    Format(T2.Amount, 'c', 'en-us') as Amount2
FROM Table1
WHERE  .....
    AND (T1.Amount>= CAST(@Amount AS INT) OR T2.Amount >= CAST(@Amount AS INT)) 
    AND @Amount IS NOT NULL

If the @Amount parameter is not NULL and is numeric the query works perfectly, but if the @Amount parameter is '%' or NULL I get the above conversion error.

Any idea on how to solve that?

Upvotes: 0

Views: 1939

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Why don't you use the proper int type for the @Amount parameter? Why do you use varchar?

I would make the type of the @Amount parameter int and pass actual value or NULL. Passing NULL would indicate that this parameter should be ignored.

@Amount int = NULL

SELECT .....
    Format(T1.Amount, 'c', 'en-us') as Amount1, 
    Format(T2.Amount, 'c', 'en-us') as Amount2
FROM 
    T1 ... JOIN T2 ...
WHERE  
    .....
    AND (@Amount IS NULL OR T1.Amount >= @Amount OR T2.Amount >= @Amount) 
OPTION (RECOMPILE)

This type of the query is called Dynamic Search Condition. I recommend to read this article by Erland Sommarskog for detailed explanations and why OPTION (RECOMPILE) is important for this type of queries.

Upvotes: 0

Tanner
Tanner

Reputation: 22733

Simple approach to check if the value is numeric:

DECLARE @Amount VARCHAR(20)= '23';

IF ISNUMERIC(@Amount) = 0
    BEGIN   
        PRINT 'Amount is null or is not a number';
    END;
ELSE
    BEGIN
        PRINT 'Amount: ' + @Amount;
    END;

To do it in the WHERE clause:

DECLARE @Amount VARCHAR(20)= '23';

SELECT 'Some Data' AS Result
WHERE ISNUMERIC(@Amount) = 1 AND @Amount > 0

Upvotes: 2

Related Questions