By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

How to retrieve all records with some field value=null

P: n/a
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
------
May 30 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
------
May 30 '07 #2

P: n/a
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
------
May 30 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.