Post reply

Concatenation

  • March 21, 2005 at 9:45 am

    #163860

    This is something simple I just cant get.

    Im trying to concatenate a counter to  a static title. And I get an error messahe. Does anyone see what im doing wrong.

     

    CREATE PROCEDURE [dbo].[sp_populate_product] AS

    declare

     @counter int

     set @counter = 1

    while @counter <> 60000

     begin

      insert into dim_product(product_key, product, package_size_key, package_type)

      values(@counter, 'product_' + @counter, @counter, 'product_type_' + @counter)

     

      set @counter = @counter + 1

     end

    GO

    Thanks

     

     

  • March 21, 2005 at 9:59 am

    #548041

    @Counter is a int. You're using the '+' sign with an int, so Sql Server thinks you're trying to perform arithmetic addition, not string concatenation. Convert it to a varchar:

    'product_' + cast(@counter as varchar)

  • March 21, 2005 at 10:03 am

    #548044

    thanks...that was it

     

    thanks

  • March 22, 2005 at 11:27 am

    #548262

    Minor quibble, but you're using "<> 60000" for looping.  This is a risky practice, not so much for simple stuff like your sample code, but in a complicated program it could be deadly.  You would be much safer if you used "<= 60000" instead.  Same result, but if somehow the value of your counter jumped to 60001, your original code would run wild.  You're always safer using <= for loops.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • March 22, 2005 at 12:30 pm

    #548300

    ... adding to the "Minor quibble" ... I'd prefer to call it a 'best practice'. Why not take it one step further and declare the 'limit' for your 'while loop' as a parameter for the stored procedure. It may save some seemingly insignificant maintenance later.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 22, 2005 at 4:38 pm

    #548345

    Yeah, definitely a 'best practice'.  My mind set is divided between classical computer programming training (cobol & fortran) and SQL's set orientation where I can't recall having implemented a counter-controlled loop structure.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • March 23, 2005 at 9:24 am

    #548529

    Give it time ... Good programing principals never die, (initialization/process/wrap-up still apples) they just undergo 'paradigm and terminology' shifts ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 7 posts - 1 through 6 (of 6 total)

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