SQL Server Variables

Question

Why do these queries return different values? The first returns a set of results, 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

This is because they are not the same query - your variable text does not fall into the query.

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

 select top 1 * from myTable 

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

EDIT: Refer to comments on a 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 must tell me where I am distorting your actual data / request in order to get additional help (or just expand it to find a solution).

Answer 2

In the first query, you check the value of "columnName" for the parameters IS NOT NULL and length> 0. In the second query, you check the values ​​in the columnName column with these parameters.

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

Answer 3

They do not match - the first checks the variable, and the second checks the column. " where @c is not null " means that the @c variable @c not null - this is not so, because it contains the value "columnName". " where columnName is not null " means that the columnName field does not contain a null . And the same for estimating the length.

Answer 4

The first request is actually evaluated as

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

Not that you were hoping for the unexpected.

Answer 5

The two queries do not match, because in the second query you evaluate the actual value of the field column name. 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