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
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.
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.
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.
VARCHAR is a variable length string data type, so it holds only the characters you assign to it.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With