Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Varchar variable is not working in WHERE clause

Why does this work...

DECLARE @MyInt int = 12345;
SELECT * FROM MyTable WHERE MyId = @MyInt; --Returns 1 row
SELECT * FROM MyTable WHERE MyId = 12345;  --Returns 1 row

but this doesn't?

DECLARE @MyVarchar varchar = 'ABCDEF';
SELECT * FROM MyTable WHERE MyId = @MyVarchar; --Returns 0 rows
SELECT * FROM MyTable WHERE MyId = 'ABCDEF';   --Returns 1 row

SQL Server version is 10.50.1746

like image 443
Joe Phillips Avatar asked Dec 20 '11 18:12

Joe Phillips


People also ask

Can I use variable in where clause SQL?

You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.

How do I declare VARCHAR?

Example - Declare a variable For example: DECLARE @techonthenet VARCHAR(50); This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype, with a length of 50 characters.

Can we store in VARCHAR?

VARCHAR the variable-length character data type VARCHAR columns, as the name implies, store variable-length data. They can store characters, numbers, and special characters just like a CHAR column and can support strings up to 8000 bytes in size.

Is VARCHAR a variable?

VARCHAR is a variable length string data type, so it holds only the characters you assign to it.


1 Answers

Because when you declare, default varchar length is 1. So @MyVarchar ends up being 'A'.

This is different to cast(something as varchar), where default length is 30.

The right thing is

DECLARE @MyVarchar varchar(10) = 'ABCDEF';

where 10 is the length of the column in the table.

like image 87
GSerg Avatar answered Oct 11 '22 14:10

GSerg