Problem with setting variable values in a loop

In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.

This is the current structure:

SET @.MktNbr = 10

WHILE @.MktNbr < 90

BEGIN

DECLARE @.sqlstmt varchar(1000)

SET @.Market = '0' + CONVERT(char(2),@.MktNbr)

SET @.sqlstmt = ' SELECT (columns)
INTO dbo.table' + @.Market + '
FROM #table
WHERE marketcode = ''' + @.Market + '''
IF @.MktNbr = 50
BEGIN
SET @.MktNbr = 51
END
ELSE
IF @.MktNbr = 51
BEGIN
SET @.MktNbr = 52
END
ELSE
IF @.MktNbr = 52
BEGIN
SET @.MktNbr = 55
END
ELSE
IF @.MktNbr = 55
BEGIN
SET @.MktNbr = 60
END
ELSE
BEGIN
SET @.MktNbr = @.MktNbr + 10
END
EXEC (@.sqlstmt)

END

I'm probably having a blonde moment, but I'm trying to replace the if statements with this:

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

Clearly it's wrong because the proc bombs every time with a duplicate table error.

It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?That works fine for me:

DECLARE @.MktNbr int

SET @.MktNbr = 30

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

PRINT @.MktNbr|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?|||Clearly it's wrong because the proc bombs every time with a duplicate table error.

Clearly

You can only execute it once per table creation.

Also, again, the assignmnet is out of whack

You will always be trying to create the same table, over and over, because the tablename is not being included in your "logic"|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
My guess is that it fails on dbo.table081, right?

When @.MktNbr reaches 81, your case statement assigns it the new value of 81. The loop will try to make table081 again and fails.
You should set it to 90, so the loop will end.|||The answer is:
@.MktNbr never exceeds 81.|||the biggest wtf here is why are there so many market tables? why not just one?|||But as Brett (and now Rudy... Man I'm slow resonding to this thread) as highlighted above - the code is not good!
Even if you have a fix this is not the way for you to be doing this - explain what you're trying to achieve and hopefully we can prod you towards a better solution :)|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?

Fair points...allow me to address them in turn.

First: yes, dynamic SQL can be yucky but this is not something I developed, I am only making a modification to it. ;)

Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

Third: I've had some ideas of things I'm going to try there so I'll get back to you on that. :)|||Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

There's a reason for that|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?|||There's a reason for that

Yep, there is. Thing is, the bossman doesn't want me to re-write the proc since it works...it's just slow. Right now the priority is just to make that amendment.

If you think that's good, there's some other ones that'd probably turn your hair white.|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?

Don't fret, the person who suggested that I needed to set the variable to 90 was right; it works now. :cool:

Some of the procs do have hard-coded statements. Some of the developers here prefer the dynamic sql because they feel it's easier to maintain. I'm new here so I'm not in a position to tell them their code sucks, particularly since I'm the least experienced of the group. And yes, the tables get dropped; that's the first thing that happens in the proc. Right now we're not doing any design changes.|||that'd probably turn your hair white.

too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em|||too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em

This particular proc takes over an hour to execute.

Right now I'm testing one that has been executing for over four hours. It's obscene. :Ssql