How to check more then one row for null by a value passed in

  • Hi all,

    I have a stored procedure that returns all records that match a certain criteria, i only want to show the records where "ConDate" is not null but i have noticed when i run this procedure say for example i have a record with three rows in it if the "Condate" in the first row isnt null but the other two are it returns it! which is incorrect i want to some how loop though each row and check all "ConDate" to make it has a value in it, if i does return the record if it doesnt then dont return it?

    this is my procedure

    Create Procedure [dbo].sp_GetDespatchPending

    @UserAbbr varchar(3),

    @ViewAllLead bit,

    @Location smallint,

    @Responsibility smallint,

    @Port smallint,-- this is the type of connection wrongly named Port

    @NetWork varchar(8)

    As

    Begin

    Declare @CanViewAllLead bit

    Declare @IsAdmin smallint

    set @CanViewAllLead = (select CanViewAllLead from dbo.UserAccount nolock where ShortAbbr = @UserAbbr)

    set @IsAdmin = (select Responsibility from dbo.UserAccount nolock where ShortAbbr = @UserAbbr)

    Select distinct CO.OrderID,CO.OrderGuid, CO.GPAddedDate,CUS.CustomerFirstname + ' ' + CUS.CustomerSurname as CustomerName, CUS.CompanyName,

    CUS.MobileNumber,USR.FirstName +' '+USR.Surname as CreatedBy from CustomerOrders CO(nolock)

    join OrderHandsets oh on oh.OrderID = co.OrderID

    join Customer(nolock) CUS on CUS.CustomerID = CO.CustomerID

    join UserAccount USR on USR.ShortAbbr = CO.CreatedBy

    where CO.OrderStatus = 4 and co.Network like @NetWork

    and CO.DespatchDate is null

    and convert(date,oh.ConnectedDate) >= '2012-06-08' --- CONVERT(CHAR(8), GETDATE(), 112) --'2012-06-01'

    and CO.OrderID in (Select OrderID from OrderHandsets(nolock) where ConDate is not null or ConDate <> '') --This is where i need to check each row

    and ((@Port =0)

    or(@Port = 3 and CO.OrderID in (Select OrderID from OrderHandsets(nolock) where OrderType = @Port and PACCode is not null))

    or(@Port <> 3 and CO.OrderID in (Select OrderID from OrderHandsets(nolock) where OrderType = @Port))

    )

    and( ((@ViewAllLead = 1 and @CanViewAllLead = 1)or(@ViewAllLead = 0 and @IsAdmin=5))

    or ( @ViewAllLead = 0 and @CanViewAllLead = 0 and CO.CreatedBy =@UserAbbr)

    )

    End

    GO

    Iv put a comment "--This is where i need to check each row" where i need to check all the rows..

    Can someone please help me?

    Thanks in advance

  • I'm not sure I understand the following bolded areas:

    .Netter (8/23/2012)


    Hi all,

    I have a stored procedure that returns all records that match a certain criteria, i only want to show the records where "ConDate" is not null but i have noticed when i run this procedure say for example i have a record with three rows in it if the "Condate" in the first row isnt null but the other two are it returns it! which is incorrect i want to some how loop though each row and check all "ConDate" to make it has a value in it, if i does return the record if it doesnt then dont return it?

    Returns what? Not null row, all rows, null rows?

    Since you comparing agains empty string I take it that Condate stored as text, not any DATE datatype, right?

    --Vadim R.

  • If I understand the problem correctly, try adding this to the outer part of your main query (copied from your subquery):

    and (OH.ConDate is not null or OH.ConDate <> '')

    Oh, and are you sure you want an 'OR' in this (and in your subquery)? Shouldn't this be an 'AND'?

  • and CO.OrderID in (Select OrderID from OrderHandsets(nolock) where ConDate is not null or ConDate <> '') --This is where i need to check each row

    You should change this to use EXISTS.


    As a side note, why all the nolock hints? Not only is that generally a bad idea, you are not consistent with their usage and reference the same table multiple times in this query, sometimes using that hint and sometimes not. :w00t:

    http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply