Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: 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:

    Code:
    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:

    Code:
    	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?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That works fine for me:
    Code:
    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
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by disruptivehair
    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"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by disruptivehair
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The answer is:
    @MktNbr never exceeds 81.
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the biggest wtf here is why are there so many market tables? why not just one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Brett Kaiser
    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.

    Third: I've had some ideas of things I'm going to try there so I'll get back to you on that.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by disruptivehair
    Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here.
    There's a reason for that
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Brett Kaiser
    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.

  13. #13
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Brett Kaiser
    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.

    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.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by disruptivehair
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by Brett Kaiser
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •