Considering empty string to null in query

Question

I am using sql server R2. I have a stored procedure in which I am passing two paramaters as @username varchar(100) and @password varchar(100).

Now, when I create user from my application first time, the password will be NULL.

I want to make a query which return record for matching username and password. The problem is code is passing empty string to stored procedure. I want something that consider or convert empty string to NULL automatically. I already have solution for this using if condition but I want to use single query, not if condition.

EDIT :

Its a simple stored procedure :

CREATE PROCEDURE [dbo].[Pro_TblP]
(
 @userName varchar(100),
 @password varchar(100) 
)
AS
BEGIN
    IF (@password ='')
        BEGIN
            SELECT * FROM TblP 
            WHERE username = @userName AND password IS NULL AND IsDeleted = 0
        END

    ELSE 
        BEGIN
            SELECT * FROM TblP 
            WHERE username = @userName AND password = @password AND IsDeleted = 0
    END
END

GO

I want to combine the query in single representation. Don't want the if condition.

We can help if you show us your code. What does it look like? — JLRishe
– JLRishe, Commented Mar 11, 2013 at 5:08

Ravi Singh · Accepted Answer · 2013-03-11 05:34:31Z

1

CREATE PROCEDURE [dbo].[Pro_TblP]
(
 @userName varchar(100),
 @password varchar(100) 
)
AS
BEGIN
   SELECT * FROM TblP 
   WHERE username = @userName AND COALESCE(password,'') = @password AND IsDeleted = 0

END

GO

SQL Fiddle

edited Mar 11, 2013 at 5:34

answered Mar 11, 2013 at 5:24

Ravi Singh

2,08013 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ravi Singh Over a year ago

@Dev : Check Fiddle and tell me where are you facing the problem?

Patrick D'Souza · Accepted Answer · 2013-03-11 05:29:38Z

0

Try this

alter PROCEDURE [dbo].[Pro_TblP]
(
 @userName varchar(100),
 @password varchar(100)='' 
)
AS
BEGIN
   BEGIN
      SELECT * FROM TblP 
      WHERE username = @userName AND IsNull(password,'')=@password AND IsDeleted = 0
   END
END

answered Mar 11, 2013 at 5:29

Patrick D'Souza

3,5592 gold badges25 silver badges40 bronze badges

2 Comments

Dev Over a year ago

Where are you comparing password with parameter @password in above stored procedure you have given?

Patrick D'Souza Over a year ago

@Dev: Updated it, was a typo when copy from my query analyser

score 0 · Accepted Answer · 2013-03-11 05:47:35Z

0

Try below code : when @password is empty string OR null it will return all values. when you pass a string in @password variable it will then filter it.

CREATE PROCEDURE [dbo].[Pro_TblP]
(
 @userName varchar(100),
 @password varchar(100) 
)
AS
BEGIN
     SELECT * FROM TblP 
         WHERE username = @userName AND IsDeleted = 0
         And (password = @password OR  isnull(@password,'') = '')           

END

edited Mar 11, 2013 at 5:47

answered Mar 11, 2013 at 5:28

user2063626

Comments

Mikael Eriksson · Accepted Answer · 2013-03-11 06:48:41Z

0

SELECT *
FROM TblP 
WHERE username = @userName AND 
      IsDeleted = 0 AND
      (
        password = @password OR
        password IS NULL AND @password = ''
      )

answered Mar 11, 2013 at 6:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Considering empty string to null in query

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related