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.