SQL varchar ‘bug’

I was having a hard time figuring out why my T-SQL query wasn’t working.

DECLARE @billid int, @cli varchar;
SET @billid =12345;
SET @cli = ‘02077778888’;

SELECT COUNT(*) FROM Records WHERE BillID = @billid AND CLI = @cli;

This query consistently returned a result of zero records, despite my knowing that records existed for this combination, e.g.:

SELECT COUNT(*) FROM Records WHERE BillID = 12345
AND CLI = ‘02077778888’;

would return the correct number.

Solution:

The length of the varchar had not been specified. Although it parses correctly it doesn’t actually work without a length being specified. This simple change fixed things:

DECLARE @billid int, @cli varchar(14);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s