How to retrieve all records with some field value=null

[=?Utf-8?B?ZGF2aWQ=?=]

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
------

[Steve C. Orr [MCSD, MVP, CSM, ASP Insider]]

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" <da***@discussions.microsoft.comwrote in message
news:40**********************************@microsof t.com...

>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
------

[=?Utf-8?B?ZGF2aWQ=?=]

Thanks, Steve.

I will try it.

David

"Steve C. Orr [MCSD, MVP, CSM, ASP Inside" wrote:

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" <da***@discussions.microsoft.comwrote in message
news:40**********************************@microsof t.com...

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
------