CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
Dev
Dev

Reputation: 6710

Considering empty string to null in query

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.

Upvotes: 0

Views: 1602

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 0

user2063626
user2063626

Reputation:

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

Upvotes: 0

Ravi Singh
Ravi Singh

Reputation: 2080

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

Upvotes: 1

Patrick D'Souza
Patrick D'Souza

Reputation: 3573

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

Upvotes: 0

Related Questions