CODEWITHSUNDEEP
sql
General Grey
General Grey

Reputation: 3688

I can't find error in basic select statement

Look at my where clause, one of them is commented out.

If I run this as is, I get a result of 0

but if I change which WHERE clause I use it works. I don't understand, @Username equals 'Kleg', yet they obviously are not. Can someone tell me where I am making my mistake

declare @UserName as varchar
set @UserName='Kleg'

declare @return as int
 set @return=(SELECT  userId
        FROM    users
        WHERE   UserName='Kleg')
--        WHERE   UserName=@Username)


SELECT  COALESCE(@return,0)

I made my query even more basic, this one is really stumping me, can someone else try this and see if they can duplicate my problem, maybe the problem is in the server

declare @UserName as varchar
set @UserName='Kleg'

if (@UserName = 'Kleg')
select 1
else select 0

this returns 0

Upvotes: 0

Views: 77

Answers (4)

Monk3y
Monk3y

Reputation: 1

You need to state how many characters are in your varchar. Change your code to the below and it should work:

declare @UserName as varchar(50)
set @UserName='Kleg'

if (@UserName = 'Kleg')
select 1
else select 0

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40309

I did some testing, it's not 100% certain, but it looks very much like when you say

declare @UserName as varchar

SQL sets up the variable as a varchar(1), meaning you where clause is searching for the value "K". Try this:

declare @UserName as varchar(50)

or, better, the length of the UserName column.

Upvotes: 0

Jonathan Moosekian
Jonathan Moosekian

Reputation: 562

Since you only have @UserName declared as varchar, if you select @UserName you will see it only returns the first character.

Upvotes: 3

Bridge
Bridge

Reputation: 30651

Try substituting for this, noting the change in how I assigned the variable:

declare @UserName as varchar
set @UserName='Kleg'

declare @return as int
SELECT @return = userId
 FROM    users
 WHERE   UserName='Kleg')
--        WHERE   UserName=@Username)

SELECT  COALESCE(@return,0)

Note it won't work if there is more than one match with that where clause!

Upvotes: 0

Related Questions