Function Not Working - 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 2008 Forums

Transact-SQL (2008)

Function Not Working

Author

Topic

barryNHS
Starting Member

3 Posts

Posted - 2015-04-14 : 13:11:34

Can anyone tell me what I've done wrong with the following function? I suspect it's something obvious that I just cant see.

quote:

CREATE FUNCTION [dbo].[CheckLoginDetails_Username](@SuppliedUsername nvarchar(150), @SuppliedPassword nvarchar(150)) RETURNS bit

AS

BEGIN

DECLARE @Result bit
DECLARE @MyUsername nvarchar(150)
DECLARE @MyPassword nvarchar(150)

SELECT @MyUsername = Username FROM Users WHERE Username = @SuppliedUsername
SELECT @MyPassword = Password FROM Users WHERE Username = @SuppliedUsername

IF ISNULL(@MyUsername,'') = ''
BEGIN
SELECT @Result = 0
END
ELSE
IF @MyPassword = @SuppliedPassword
BEGIN
SELECT @Result = 1
END
ELSE
BEGIN
SELECT @Result = 0
END

RETURN @Result

END

The trouble lies with the 2 lines immediately after the variable declarations. @MyUsername and @MyPassword are never being set despite the fact that the correct values are being passed to the function and there is a matching record in the Users table.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-14 : 13:58:41

Show us how you are executing the function. Combine the two lines you indicated into one statement for performance reasons. There isn't an issue with those lines of code though. I suspect the issue is with the execution of the function.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-14 : 14:34:31

[code]
CREATE FUNCTION [dbo].[CheckLoginDetails_Username] (
@SuppliedUsername nvarchar(150),
@SuppliedPassword nvarchar(150)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE
WHEN @SuppliedPassword = (SELECT Password FROM dbo.Users WHERE Username = @SuppliedUsername)
THEN 1
ELSE 0 END AS Result
)
END

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-15 : 02:46:28

[code]CREATE FUNCTION dbo.CheckLoginDetails_Username
(
@SuppliedUsername NVARCHAR(150),
@SuppliedPassword NVARCHAR(150)
)
RETURNS BIT
AS
BEGIN
RETURN (
SELECT COUNT(*)
FROM dbo.Users
WHERE Username = @SuppliedUsername
AND [Password] = @SuppliedPassword
)
END[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources