Difference in SQL queries with or without parameters

Question

Can anyone tell me what the difference (besides the obvious) is between these two queries: 1)

declare @coy_oid varchar
declare @field_name varchar

set @coy_oid = '10'
set @field_name = 'ResultReason'

SELECT OID, POSITION, DESCRIPTION, FIELD_NAME 
FROM T_FIELD_TEXT_CHOICES 
WHERE COY_OID = @coy_oid AND FIELD_NAME = @field_name

2)

declare @coy_oid varchar
declare @field_name varchar

set @coy_oid = '10'
set @field_name = 'ResultReason'

SELECT OID, POSITION, DESCRIPTION, FIELD_NAME 
FROM T_FIELD_TEXT_CHOICES
WHERE COY_OID = @coy_oid AND FIELD_NAME = 'ResultReason'

The first one returns nothing and the second returns the expected results. I am sure it has to do with the FIELD_NAME being a variable, but I don't know why.

Guess I should add this is SQL Server 2008 R2, but maybe it doesn't matter.

you need to define a size while defining the type as char/varchar/nvarchar — Rahul
– Rahul, Commented Jun 29, 2015 at 20:37

Amit · Accepted Answer · 2015-06-29 20:33:59Z

8

You're variables are declared as varchar. That's a single character, so in the first query you're comparing with 'R'. You probably meant to use something like varchar(100)...

answered Jun 29, 2015 at 20:33

Amit

46.5k9 gold badges84 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy J Over a year ago

That makes perfect sense now.

gen-y-s Over a year ago

hahaha, so varchar means varchar(1) ??? what a stupid default, not to mention the lack of warning for truncation......

Russell · Accepted Answer · 2015-06-29 21:00:29Z

3

varchar is the equivalent of varchar(1)

To see this consider

DECLARE @v1 VARCHAR
SET @v1 = '12345'

DECLARE @v2 VARCHAR (5)
SET @v2 = '12345'

SELECT @v1 AS v1, @v2 AS v2

which returns

'1' for v1 
'12345' for v2

answered Jun 29, 2015 at 21:00

Russell

5992 gold badges7 silver badges20 bronze badges

Comments

SQL Police · Accepted Answer · 2015-06-29 20:49:18Z

0

Solution:

 declare @coy_oid varchar(100)
 declare @field_name varchar(100)

answered Jun 29, 2015 at 20:49

SQL Police

4,1961 gold badge28 silver badges57 bronze badges

Collectives™ on Stack Overflow

Difference in SQL queries with or without parameters

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related