check sql validation

Nandha Kumar Nagarajan

7y930

this is my sp

CREATE PROCEDURE USP_LOGIN

(

@UNAME VARCHAR(100),@PASS VARCHAR(100)

)

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT *FROM TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS)

PRINT 'LOGIN SUCCESFULLY'

ELSE

PRINT 'INCORRECT USERNAME OR PASSWORD'

END

how can i check a username and password validation

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gajendra Jangid7y ago
hello
CREATE PROCEDURE USP_LOGIN
(
@UNAME VARCHAR(100),@PASS VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON;
if len(rtrim(ltrim(@UNAME)))=0
PRINT 'Please Enter User Name'
else if len(rtrim(ltrim(@PASS)))=0
PRINT 'Please Enter User Name'
else IF EXISTS(SELECT *FROM TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS)
PRINT 'LOGIN SUCCESFULLY'
ELSE
PRINT 'INCORRECT USERNAME OR PASSWORD'
END
Amit Kumar7y ago
Hi,
If you want to check the validation then before check exists both are (@UNAME & @PASS) NOT NULL Like.
CREATE PROCEDURE USP_LOGIN
(
@UNAME VARCHAR(100),@PASS VARCHAR(100)
)
AS
BEGIN
IF(@UNMA IS NOT NULL  AND @PASS IS NOT NULL)
BEGIN
IF   EXISTS(SELECT * FROM TBL_LOGIN WHERE USERNAME=
        @UNAME AND PASWORD=@PASS)
    PRINT 'LOGIN SUCCESFULLY'
ELSE
    PRINT 'INCORRECT USERNAME OR PASSWORD'

END
I hope it will help you.
Jignesh Kumar7y ago
Hi,

Please try below code,
CREATE PROCEDURE USP_LOGIN
(
@UNAME VARCHAR(100),@PASS VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT *FROM TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS)
SELECT 'true' AS UserExists
ELSE
SELECT 'false' AS UserExists
END
Call above Store procedure in your code and Check your condition for true or false. if true user is exists in database and fales means user does not exists in database.
Rupesh Kahane7y ago
1) way 1
Declare
@UNAME VARCHAR(100) = 'Sam',
@PASS VARCHAR(100) = '123'

DECLARE @TBL_LOGIN TABLE (
                    USERNAME varchar(20)
                    ,PASWORD  varchar(20)
                    ,Is_Delete int not null default 0
                    )

Insert Into @TBL_LOGIN values('Sam','123',0)
Insert Into @TBL_LOGIN values('Rupesh','456',0)
Insert Into @TBL_LOGIN values('Rajesh','987',0)
Insert Into @TBL_LOGIN values('Amol','321',1)

--Select * from @TBL_LOGIN where Is_Delete = 0

IF ((SELECT Count(*) FROM @TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS AND Is_Delete = 0) > 0)
begin
PRINT 'LOGIN SUCCESFULLY'
end
ELSE
begin
PRINT 'INCORRECT USERNAME OR PASSWORD'
END
2) Way 2
Declare
@UNAME VARCHAR(100) = 'Sam',
@PASS VARCHAR(100) = '123'

DECLARE @TBL_LOGIN TABLE (
                    USERNAME varchar(20)
                    ,PASWORD  varchar(20)
                    ,Is_Delete int not null default 0
                    )

Insert Into @TBL_LOGIN values('Sam','123',0)
Insert Into @TBL_LOGIN values('Rupesh','456',0)
Insert Into @TBL_LOGIN values('Rajesh','987',0)
Insert Into @TBL_LOGIN values('Amol','321',1)

--Select * from @TBL_LOGIN where Is_Delete = 0

IF EXISTS(SELECT *FROM @TBL_LOGIN WHERE USERNAME= @UNAME AND PASWORD=@PASS AND Is_Delete = 0)
begin
PRINT 'LOGIN SUCCESFULLY'
end
ELSE
begin
PRINT 'INCORRECT USERNAME OR PASSWORD'
END