While loop

Question

Post reply

While loop

Viewing 5 posts - 16 through 19 (of 19 total)

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

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 1

I am not fixated on a while loop.Anything more efficient will work just fine.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

@Brian Gale.

That would update all rows where Q=1 in the table which is not what is needed.Every time the stored procedure executes it will populate the same table with as many rows as per the parameters provided. If Q =1 then all rows where Q=1 will be updated thats not what is needed.

exec [dbo].[ABCD] 1,0, 1,2,'2020-12-31 22:30',1,'','',1
                  M D  F Q  T                 R       IO

TQRRR
12/31/20 22:302112/31/20 22:30
1/1/21 22:301112/31/20 22:30
1/2/21 22:302012/31/20 22:30
1/3/21 22:301012/31/20 22:30

exec [dbo].[ABCD] 1,0, 1,2,'2020-01-02 06:30',1,'','',1
                  M D  F Q  T                 R       IO

TQRRR
12/31/20 22:30211/2/20 6:30
1/1/21 22:30111/2/20 6:30
1/2/21 22:30201/2/20 6:30
1/3/21 22:30101/2/20 6:30
1/2/20 6:30211/2/20 6:30
1/3/20 6:30111/2/20 6:30
1/4/20 6:30201/2/20 6:30
1/5/20 6:30101/2/20 6:30

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 3

@Brian,

Are CTE's reallt the way to go for this . Every time I execute the stored procedure they are probably going to generate min(2 rows) or max (30 rows) and populate the same table.

This reply was modified 3 years, 10 months ago by mtz676.
This reply was modified 3 years, 10 months ago by mtz676.
This reply was modified 3 years, 10 months ago by mtz676.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

CTE's are not the only way to do it. Nested selects would be another option, or you could do the calculation outside of SQL at the application layer, or you could use temp tables or table variables... there are other options. Heck, even Sergiy's approach doesn't use CTE's and has the added benefit of a single INSERT with no UPDATE which is a HUGE performance benefit.

My approach is CTE's as they are easier to debug and faster for coding than temp tables or table variables. I find nested selects to be harder to debug, but that is more of a personal preference.

I say, use whatever option you think is best for your situation.

As for the Q=1 part, In your example it looks like when Q=1, that is when you want to do the update, no? If you are just wanting to update the NEW values, not the old ones, then I think that Sergiy's approach is going to be a lot more simple and give you the results you need as well as having a performance boost as you don't need to do things in 2 steps.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 5

mtz676

SSCrazy Eights

Points: 9212

January 11, 2021 at 3:22 am

#3830714

Thank you