CASE statement - 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

SQL Server Development (2000)

CASE statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-17 : 08:26:27

Ron writes "I am writing a user defined function and would like to use a CASE statement versus an if then statement to return an ID as follows

CREATE FUNCTION fnUserName
((@fname varchar(10)
@lname varchar(20))
RETURNS int
AS
BEGIN
DECLARE @ID=int, @row = int
Select @ID = EmployeeID FROM Employees
WHERE FirstName = @fname AND LastName = @lname

SET @Row = @@RowCount

CASE @Rows
When >1 THEN @ID = -1
WHEN =0 THEN @ID = 0
END CASE

RETURN @ID
END

When I run this I get a syntax error near CASE"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-17 : 08:30:40

In SQL, CASE is not a statement, it's an expression. It does not control program flow. It evaluates logical conditions and returns values based on those conditions (like a function). You can write your code like:

SET @ID=CASE WHEN @Rows>1 THEN -1 ELSE 0 END

However, it would be even more efficient to write it as:

CREATE FUNCTION fnUserName(@fname varchar(10), @lname varchar(20))
RETURNS int AS
BEGIN
RETURN (CASE WHEN EXISTS(SELECT * FROM Employees WHERE FirstName=@fname AND LastName=@lname) THEN -1 ELSE 0 END)
END

If all you want to do is check for a row, EXISTS is more efficient than using COUNT() or @@ROWCOUNT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources