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

Question

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

.Netter

SSCrazy

Points: 2476
More actions
August 23, 2012 at 1:43 am

#260985

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

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

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

rVadim Hall of Fame Points: 3969 More actions · Answer 1

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.

nellisjp SSChasing Mays Points: 624 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/