Results 1 to 6 of 6

Thread: tsql help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: tsql help

    I have a few variables declared

    @amount=100000 (any big int)
    @pamount=100000
    @a1,@a2 - thorugh @25 -- each a bigint
    @p1,@p2,@p3
    @id - int

    if @id is an odd number I need to randomly change @amount-@a1 (randomly to @a1 - @a25 so it ends up being any of 25 values)
    and then @pamount randomly= to either @p1,@p2 or @p3


    Can someone help me do the sql for this?
    it will be within my stored procedure

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Because I am not sure exactly what you are doing I cannot be certain but I doubt very much you are doing this the best and most flexible way.

    Please could you give a non technical explanation of what you are trying to do especially with regard to the eventual goal of all this? I suspect you have got to a point where you think you have the answer but don't know how to realise it whereas it would be useful to get a perspective of the big picture.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    thanks - basically i'm trying to change amount to one of 25 values(randomly) and pamount to 1 of 3 values if the @id is an odd number

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol. Well - I was hoping for something a bit broader.

    Ok - this does not do exactly what you want. BUT it covers all the techniques you would need to extend it to everything you want.... I think - because I still don't know what your eventual goal is.

    This gist is to work with a table (in a similar way to an array) rather than 25 + seperate variables.

    Code:
     
    DECLARE @t TABLE
    (
    myval INT 
    , replaces_variable CHAR(3)
    )
     
    DECLARE @id AS INT
    , @pamount AS INT
     
    SELECT @id = 10
    , @pamount = 100000
     
    INSERT INTO @t (myval, replaces_variable)
    SELECT 1, '@p1'
    UNION ALL
    SELECT 3, '@p2'
    UNION ALL
    SELECT 5, '@p3'
     
    SELECT @pamount = CASE WHEN @id % 2 = 0 THEN @pamount ELSE @pamount - myval END
    FROM @t
    ORDER BY NEWID()
     
    PRINT '@id is EVEN'
    PRINT @pamount
     
    SELECT @id = 5
     
    SELECT @pamount = CASE WHEN @id % 2 = 0 THEN @pamount ELSE @pamount - myval END
    FROM @t
    ORDER BY NEWID()
     
    PRINT '
    @id is ODD'
    PRINT @pamount



    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2006
    Posts
    66
    ok thanks .. this is a good idea to do in a table - i'll try it this way

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    From where do you get the values for your variables?

    Do you use a cursor to select @id, @amount and @pamount
    and then update using data stored in your code (e.g. @a1 to @a25)?

    Don't use a cursor and don't store your data in code - use tables

Posting Permissions

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