February 7, 2013 at 5:59 pm
Here is my sample data table
Table Name = Test
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18
3,111,12,24
Here is my SP (Inside SP)
-- I am update above table
Update Test
Set UpdateField = Right
from Test
where MinDuration < 12
Update Test
Set UpdateField = Left
From Test
Where MinDuration >= 12
My Question is :- I want when i run above store procedure if First Update Complete don't go to second update (For E.g if First Creteria meet the
requirement and update "UpdateField to Right" don't go to Next Update Statement.
Any help would be great appreciate.
Thank You.
February 7, 2013 at 6:06 pm
Based on the sample data, what would be the state of the data after running the procedure?
I can't figure out what it is you want from your description.
February 7, 2013 at 6:57 pm
Let me correct something for make more sense.
Instead of Right and Left, Is High and Low.
Here is my SP (Inside SP)
-- I am update above table
Update Test
Set UpdateField = Low
from Test
where MinDuration < 12
Update Test
Set UpdateField = High
From Test
Where MinDuration >= 12
Note:- I am using this SP Through front end if user search through ID, it will show High or Low.
What i want if first Update complete and update Table/Column don't go to next Update.... Please let me know if you need more info....
February 7, 2013 at 7:03 pm
rocky_498 (2/7/2013)
Let me correct something for make more sense.Instead of Right and Left, Is High and Low.
Here is my SP (Inside SP)
-- I am update above table
Update Test
Set UpdateField = Low
from Test
where MinDuration < 12
Update Test
Set UpdateField = High
From Test
Where MinDuration >= 12
Note:- I am using this SP Through front end if user search through ID, it will show High or Low.
What i want if first Update complete and update Table/Column don't go to next Update.... Please let me know if you need more info....
Look at your sample data in the first post. What will this data look like when your procedure completes. I still don't understand what you are describing, it would help a lot if you could show us what you expect to occur based on the sample data.
February 7, 2013 at 7:18 pm
Ok, I am sorry if i am not explain you very well.
Let me try again. I change the Query Little bit...
Table Name = Test (Here is my main Table)
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18
3,111,12,24
Here is my SP (Inside SP)
-- I am update above table
Select * into #Temp1 from Test
Update #Temp1
Set UpdateField = Low
from Test
where MinDuration = 12
Update #Temp1
Set UpdateField = High
From Test
Where MinDuration >= 12
The End Result Its Depend (Let say its meet first Update requirement) then End result should be
Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12, Low
2,345,6,18
3,111,12,24
If first Update statement doesn't meed the requirement then End result should be
Select * from #Temp1
ID,DTypeID,MinDuration,MaxDuration,UpdateField
1,123,0,12
2,345,6,18, High
3,111,12,24, High
Thank You....
February 7, 2013 at 8:24 pm
Update Test
Set UpdateField = case when MinDuration < 12 then Right else Left end
from Test
_____________
Code for TallyGenerator
February 8, 2013 at 12:01 am
If I understand correctly the Second statement shouldnt run if the first statement updates rows, if it doesnt update any rows then the second statement should run, if that is the case then In its simplest form I think this is what you are looking for
Update Test
Set UpdateField = Low
from Test
where MinDuration < 12
IF @@ROWCOUNT=0
BEGIN
Update Test
Set UpdateField = High
From Test
Where MinDuration >= 12
END
This will only run the second update IF the first one doesnt update any rows.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 9, 2013 at 12:59 pm
Jason Thanks that's what i want.
Quick question. Let say i have three update statement instead of two
Update Test
Set UpdateField = Low
from Test
where MinDuration < 12
IF @@ROWCOUNT=0
BEGIN
Update Test
Set UpdateField = High
From Test
Where MinDuration >= 12
END
IF ?
Thank You...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply