«

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

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


"david" <> wrote in message
news:40B3C8A3-A9BE-4BDD-813D-...
>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
> ------

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" <> wrote in message
> news:40B3C8A3-A9BE-4BDD-813D-...
> >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
> > ------
>