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.
| 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? |
|
|
|
|
|
|
|
|
check for not null in either of two columns