1. Registered User
Join Date
Jun 2006
Posts
66

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. King of Understatement
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.

3. Registered User
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. King of Understatement
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

5. Registered User
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. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
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
•