CODEWITHSUNDEEP
sqlsql-server-2012
Dave
Dave

Reputation: 205

Unable to return correct query when using string parameter in SQL Server

I have a simple employee table

employee table

and here is the data

employee table data

When I try to run a query with string parameter it doesn't return anything although it has correct parameter

query with correct string parameter

and when I try to change it to double quote ", it returns an error

query enclosed parameter with "" for string parameters

Have I missed something? Thanks in advance

Upvotes: 0

Views: 296

Answers (3)

Eoin2211
Eoin2211

Reputation: 911

You can simply change the data type of the empId column to Int instead of varchar.

Upvotes: 0

Matteo Avesani
Matteo Avesani

Reputation: 86

You could have a special char, like a carriage return, at the end of the value. Use the query below to check if there are some of those cases in your table.

    SELECT
       EmpId,
    --   convert(int, EmpId) AS EmpId_INT,
       LEN(EmpId) AS Len_EmpId
    FROM
       Employee
    -- WHERE
    --   LEN(EmpId) <> LEN(convert(int, EmpId))
    ORDER BY
       EmpId

If it's not mandatory, I suggest to change the "EmpId" data type to int.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Your first query is correct. The problem is the data. The empid field has something other than '10002' in the field. You can investigate this in various ways:

select length(empid), '|' + empid + '|'
from employee

This will provide information about unexpected characters that you might not see. The length for the field should be 6. The output for the second column should be "|100002|".

If an extra character at the beginning or end of the field is the issue, then:

where empid = '%100002%'

solves the problem. However, there is a big potential performance difference, because this query cannot take advantage of the primary key index. So, you want to fix the data.

You might have confused the letter zero with the number, or capital "I" or lower case "l" for 1. These can be harder to spot, but you can use lower() and upper() to help find such issues.

Upvotes: 2

Related Questions