How to retrieve all records with some field value=null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a web services method getRecords(string name, string alias).
In the implementation, I use the following stored procedure. How can I get
the all records with alias=null. When I leave alias empty, it returns all
records with alias != null.

Thanks for any help.

David

-----
CREATE PROCEDURE searchTable4Test
(
@Name nvarchar(64),
@Alias nvarchar (64)

)
AS
Select * from Table4Test
Where name like '%'+@Name+'%' and (alias like '%'+@Alias+'%' )
RETURN @@IDENTITY
GO
------
 
This is an ASP.NET group, not a SQL group.

But you might try this:
Where name like '%'+@Name+'%' and (IsNull(alias,'') like '%'+@Alias+'%' )

The second parameter sent to the SQL Server IsNull function is an empty
string. This tells SQL Server to convert nulls to empty strings when doing
the evaluation.

Another approach might be something like this:
Where name like '%'+@Name+'%' and (alias like '%'+@Alias+'%' or alias is
null)

Here are more examples:
http://www.lakesidesql.com/articles/?p=6
 
Thanks, Steve.

I will try it.

David

Steve C. Orr [MCSD said:
This is an ASP.NET group, not a SQL group.

But you might try this:
Where name like '%'+@Name+'%' and (IsNull(alias,'') like '%'+@Alias+'%' )

The second parameter sent to the SQL Server IsNull function is an empty
string. This tells SQL Server to convert nulls to empty strings when doing
the evaluation.

Another approach might be something like this:
Where name like '%'+@Name+'%' and (alias like '%'+@Alias+'%' or alias is
null)

Here are more examples:
http://www.lakesidesql.com/articles/?p=6

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net


david said:
I have a web services method getRecords(string name, string alias).
In the implementation, I use the following stored procedure. How can I get
the all records with alias=null. When I leave alias empty, it returns all
records with alias != null.

Thanks for any help.

David

-----
CREATE PROCEDURE searchTable4Test
(
@Name nvarchar(64),
@Alias nvarchar (64)

)
AS
Select * from Table4Test
Where name like '%'+@Name+'%' and (alias like '%'+@Alias+'%' )
RETURN @@IDENTITY
GO
------
Click to expand...
Click to expand...
 
Back
Top