SQL Server variables

Question

Why do these queries return different values? The first returns a result set as expected, but the second (which as far as I can tell is exactly the same) does not. Any thoughts?

1:

declare @c varchar(200)

set @c = 'columnName'

select top 1 * 
from myTable 
where @c is not null 
      and len(convert(varchar, @c)) > 0

2:

SELECT top 1 * 
FROM myTable 
WHERE columnName IS NOT NULL 
      and len(convert(varchar,columnName)) > 0   

Answer 1

It's because they aren't the same query -- your variable text does not get inlined into the query.

In query 1 you are validating that @c is not null (true, you set it) and that its length is greater than 0 (true, it's 10). Since both are true, query 1 becomes:

select top 1 * from myTable

(It will return the first row in myTable based on an appropriate index.)

EDIT: Addressing the comments on the question.

declare @myTable table
(
    columnName varchar(50)
)

insert into @myTable values ('8')

declare @c nvarchar(50)
set @c = 'columnName'

select top 1 * 
from @myTable 
where @c is not null 
      and len(convert(varchar, @c)) > 0

select top 1 * 
from @myTable 
where columnName is not null
      and len(convert(varchar,columnName)) > 0

Now when I run this both queries return the same result. You'll have to tell me where I'm misrepresenting your actual data / query to get more help (or just expand upon this to find a solution).

Answer 2

In the first query, you are checking the value 'columnName' against the parameters IS NOT NULL and length > 0. In the second query, you are checking the values in the columnName column against those parameters.

It should be noted that query 1 will always return one row (assuming a row exists), where query 2 will only return a row if the contents of columnName are not null and length > 0.

Answer 3

They're not the same - the first is checking the variable while the second is checking the column. "where @c is not null" means where the variable @c isn't null - which it isn't, since it contains the value 'columnName'. "where columnName is not null" means where the field columnName doesn't contain a null. And the same for the evaluation of the length.

Answer 4

The first query actually evaluates as

select top 1 * from myTable where 'columnName' is not null and len(convert(varchar, 'columnName' )) > 0

Not as what you were hoping for.expected.

Answer 5

The two querys are not the same as in the second query you are evaluating the actual value of the field columnname. The following is the equivalent of your first function.

SELECT top 1 * FROM myTable WHERE 'columnName' IS NOT NULL and len(convert(varchar,'columnName')) > 0