check for not null in either of two columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

check for not null in either of two columns

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-14 : 18:55:06

Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null

I am trying to create a stored procedure where
I need to loop through every row of the table and check if
there exists a date in either of the column. If date exists in
either of the column for every row, I need to update some other stuff.
For the above table, it meets my requirement, so I need to update.

However if the table is as below, it doesn't meet the requirement, so I don't need to update
deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null

I started off as below, But I am getting incorrect values. Could anyone please correct
Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime

set @deleterevoke = 'false'

CREATE TABLE #DELETEREVOKEDATES ( DelId int primary key identity(1,1), RevokedDate datetime, DeletedDate datetime)

INSERT #DELETEREVOKEDATES SELECT RevokedDate, DeletedDate from content
Set @DelRevId = 1
Set @DelRevnumrows = (SELECT COUNT(*) FROM #DELETEREVOKEDATES)
IF @DelRevnumrows > 0
WHILE (@DelRevId < = (SELECT MAX(DelId) FROM #DELETEREVOKEDATES))
BEGIN
SET @tempRevokedDate = (SELECT RevokedDate from #DELETEREVOKEDATES where DelId = @DelRevId)
SET @tempDeletedDate = (SELECT DeletedDate from #DELETEREVOKEDATES where DelId = @DelRevId)

IF (@tempRevokedDate is null and @tempDeletedDate is null)
BEGIN
set @deleterevoke = 'true'
END

SET @DelRevId = @DelRevId + 1
END
IF (@deleterevoke = 0)
begin
-- do some updates
end

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-14 : 19:22:32

The following is the complete stored procedure

AS
BEGIN
SET NOCOUNT ON;

Declare @i int
Declare @numrows int
Declare @Chapterid bigint
Declare @NewContentId bigint

Declare @deleterevoke bit

Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime
Declare @tempMaxRevokedDate datetime
Declare @tempMaxDeletedDate datetime
Declare @requiredDate datetime
Declare @checkRevokedDate datetime

set @deleterevoke = 'false'

CREATE TABLE #DELETEREVOKEDATES ( DelId int primary key identity(1,1), RevokedDate datetime, DeletedDate datetime)

CREATE TABLE #CHAPTERIDS ( id int primary key identity(1,1), ChapterId bigint)

Insert #CHAPTERIDS SELECT DISTINCT(CHAPTERID) FROM chapteridtable

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM #CHAPTERIDS)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(id) FROM #CHAPTERIDS))
BEGIN
SET @Chapterid = (SELECT ChapterId FROM #CHAPTERIDS where id = @i)

INSERT #DELETEREVOKEDATES SELECT RevokedDate, DeletedDate from content where parentid = @Chapterid)
Set @DelRevId = 1
Set @DelRevnumrows = (SELECT COUNT(*) FROM #DELETEREVOKEDATES)
IF @DelRevnumrows > 0
WHILE (@DelRevId < = (SELECT MAX(DelId) FROM #DELETEREVOKEDATES))
BEGIN
SET @tempRevokedDate = (SELECT RevokedDate from #DELETEREVOKEDATES where DelId = @DelRevId)
SET @tempDeletedDate = (SELECT DeletedDate from #DELETEREVOKEDATES where DelId = @DelRevId)

IF (@tempRevokedDate is null and @tempDeletedDate is null)
BEGIN
set @deleterevoke = 'true'
END

SET @DelRevId = @DelRevId + 1
END

IF (@deleterevoke = 0)
BEGIN
SET @tempMaxRevokedDate = (SELECT MAX(RevokedDate) FROM #DELETEREVOKEDATES)
SET @tempMaxDeletedDate = (SELECT MAX(DeletedDate) FROM #DELETEREVOKEDATES)

SET @requiredDate = (SELECT CASE WHEN ISNULL(@tempMaxRevokedDate, 0) < ISNULL(@tempMaxDeletedDate,0)
THEN @tempMaxDeletedDate ELSE @tempMaxRevokedDate END )

set @NewContentId = (select max(contentid) from content where NodeId = @ChapterId)

select @checkRevokedDate = [RevokedDate] from Content WHERE [ContentId] = @NewContentId

if (@checkRevokedDate is null)
begin
--update query

end

END

DELETE FROM #DELETEREVOKEDATES
set @deleterevoke = 'false'
SET @i = @i + 1

END

END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-14 : 19:23:13

[code]
update Content_Table
set otherstuff = otherthings
where deleteddate is null or revokeddate is null
[/code]

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-15 : 05:00:30

My stored procedure is working only if I pass in one chapterid, but not working for all the list.
The following is the requirement:

select chapterid from chapteridtable

for each chapterid

select revokeddate, deleteddate from content where contentid = chapterid
look in all the rows, if any row has both deleteddate and revokeddate as null values, then do not do anything
if all the rows has either got deleteddate or revokeddate
then get the max of revokeddate and deleteddate
update the chapteridtable with the max of revokeddate and deleteddate (update chapteridtable set revokeddate = max of revokeddate or deleteddate where chapterid = @chapterid)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-15 : 09:01:57

I'm confused. In your original post you saie:

quote:

Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null

I am trying to create a stored procedure where
I need to loop through every row of the table...

Meaning that you want to update the Content table.

Now you're talking about a different table (chapteridtable)
Which is it?

btw the way you state the problem:

quote:

look in all the rows, if any row has both deleteddate and revokeddate as null values, then do not do anything
if all the rows has either got deleteddate or revokeddate
then get the max of revokeddate and deleteddate

says to me that you are not thinking of set-based logic. "look in all the rows" is what SQL does.

It would help a lot if you would post some sample data:

1. Post some sample rows of the chapteridtable and the content table before you call your proc
2. post some sample parameters to your stored procedure
3. post the results you want, using the sample rows and sample parameters. That is, what does the chapterid table look like after calling the stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources