How to retrieve all records with some field value=null

Discussion in 'ASP .Net' started by =?Utf-8?B?ZGF2aWQ=?=, May 30, 2007.

  1. 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=?=, May 30, 2007
    #1
    1. Advertising

  2. 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:...
    >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], May 30, 2007
    #2
    1. Advertising

  3. 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:...
    > >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=?=, May 30, 2007
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    5
    Views:
    26,479
    Mike Schilling
    Mar 29, 2006
  2. Rick
    Replies:
    3
    Views:
    8,091
    Alexey Smirnov
    Apr 13, 2010
  3. Replies:
    3
    Views:
    641
    Anthony Jones
    Nov 2, 2006
  4. VUNETdotUS
    Replies:
    25
    Views:
    431
    Thomas 'PointedEars' Lahn
    Nov 10, 2007
  5. jr
    Replies:
    3
    Views:
    405
Loading...

Share This Page