if no result, return -1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

if no result, return -1

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-09-26 : 11:33:42

Hi,
I orginally thought this would be easy but I'm running into problems. I need to return the userID from a table given a username. If there is no userID found I need to return a "-1". I have tried using coalesce and isnull but they don't work. The value isn't null, it just doesn't exist. hmmmm. If there is no userID for the given username how can I return somthing other than an empty result set?


SELECT
        UserID
	--,COALESCE(UserID,-1)
	--,ISNULL(UserID,-1)
FROM 
	Users a
WHERE
	UserName = @UserName

Thanks

Nic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 11:50:51

You can try this:

SELECT
ISNULL((SELECT userID from Users WHERE username = @userName),
-1)

- Jeff

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-09-26 : 11:52:55

quote:
Originally posted by nic

If there is no userID for the given username how can I return somthing other than an empty result set?

Saying that there is no userID for a give username is different from saying that the username does not exist in the table. The first one will return a null, the second an empty resultset.

So, assuming the second is your issue, then whatever you are returning this result set to (sproc, vb code, etc.) needs to check for whether anything was returned and then set the variable value you are using in processing.

(damn, sniped!) Or you could go with Jeff's answer above.

------------------------------------------------------
The more you know, the more you know you don't know.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-26 : 12:11:38

That's Ok, you described the situation quite well!

NOTE: if the username column in the table is not unique, the above will return an error if more than one UserID matches a username.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources