CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Mohammad hossein
Mohammad hossein

Reputation: 255

search in sql with 2 input parameter

i have 2 input parameter and i want search with These

 CREATE TABLE dbo.tbl_answer
 (
  an_del INT,
  u_username NVARCHAR(50),
  u_name NVARCHAR(50) null
  )

INSERT dbo.tbl_answer
VALUES(1, 'mohammad', null), (1, 'A13A6C533AF77160FBF2862953FA4530', 'GCV'), (1, 'C', 'GG'), (0, 'AB', 'BB'), (1, 'AC', 'K')
GO

CREATE PROC dbo.SearchAnswers
@Username nvarchar(20),
@Name nvarchar(20)
AS
SELECT *  
FROM dbo.tbl_answer
WHERE an_del = 1 AND u_username LIKE ISNULL('%' + @Username + '%', u_username)
  and u_name LIKE  ISNULL('%' + @Name + '%', u_name)

and i run this command EXEC dbo.SearchAnswers 'moha', null but return any data look at this

Upvotes: 0

Views: 107

Answers (3)

Sachin
Sachin

Reputation: 2148

Here you are searching for 2 fields username and name and input parameter is null.
Like will not work for null so you have to check for null independently or put some supplementary for that...

CREATE PROC dbo.SearchAnswers
@Username nvarchar(20),
@Name nvarchar(20)
AS

declare  @Name2 nvarchar(20)
set @Name2 = ISNULL(@Name, '00')

SELECT *  
FROM dbo.tbl_answer
WHERE an_del = 1 AND ( u_username LIKE ISNULL('%' + @Username + '%', u_username)
  AND  
ISNULL(u_name,'00') LIKE  '%' + @Name2 + '%' )

SQL Fiddle

Upvotes: 0

Jignesh.Raj
Jignesh.Raj

Reputation: 5987

Try this :

CREATE PROC dbo.Answers
@Username nvarchar(20),
@Name nvarchar(20)
AS

declare  @Name2 nvarchar(20)
set @Name2 = ISNULL(@Name, '00')

SELECT *  
FROM dbo.tbl_answer
WHERE an_del = 1 AND ( u_username LIKE ISNULL('%' + @Username + '%', u_username)
  AND  
ISNULL(u_name,'00') LIKE  '%' + @Name2 + '%' )

Upvotes: 1

DougM
DougM

Reputation: 2888

Your problem is that you aren't allowing for u_name to be null in your table. Which it is, on the only record with a u_username containing "moha"

CREATE PROCEDURE dbo.SearchAnswers
@Username nvarchar(20),
@Name nvarchar(20)
AS
SELECT *  
FROM dbo.tbl_answer
WHERE an_del = 1 
AND u_username LIKE ISNULL('%' + @Username + '%', u_username)
AND ISNULL(u_name,'') LIKE  ISNULL('%' + @Name + '%', u_name)

You could also have the third conditional explicity test for NULL.

AND ( (u_name is null) or (u_name LIKE ISNULL('%' + @Name + '%', u_name) )

Upvotes: 0

Related Questions