Post reply

Store Procedure

  • February 16, 2017 at 8:57 am

    #335083

    I have a store Procedure which generate output as one random number

    I need to execute 100 times how can I do this.

    Thanks
    Nita

  • February 16, 2017 at 9:04 am

    #1928872

    What do you plan to do with this random number?

  • February 16, 2017 at 9:12 am

    #1928879

    Quick questions, in this context, what is random? Must the output be unique every time? Is there a limit for the values, i.e. 1-1000?
    😎

  • February 16, 2017 at 9:16 am

    #1928881

    Here is literally what you've asked for.

    CREATE PROCEDURE GenerateOneRandomNumber
    AS
    SELECT RAND() RandomNumber;
    GO

    EXEC GenerateOneRandomNumber;
    GO 100

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • February 16, 2017 at 9:27 am

    #1928887

    ZZartin - Thursday, February 16, 2017 9:04 AM

    What do you plan to do with this random number?

    my store procedure ( exec SP_DEV_generateanynumber)

    When I execute the above SP I get 3@8 as output

    All I want to execute this 100 times in one attmept to get 100 random number

  • February 16, 2017 at 9:53 am

    #1928893

    Nita Reddy - Thursday, February 16, 2017 9:27 AM

    ZZartin - Thursday, February 16, 2017 9:04 AM

    What do you plan to do with this random number?

    my store procedure ( exec SP_DEV_generateanynumber)

    When I execute the above SP I get 3@8 as output

    All I want to execute this 100 times in one attmept to get 100 random number

    If I understand you correctly, the stored procedure is working as needed, you just want to have it run 100 times.
    You can do this using the optional parameter of GO statement to execute T-SQL batch X number of times.

    For example:

    EXEC SP_DEV_generateanynumber;
    GO 100

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • February 16, 2017 at 10:45 am

    #1928900

    Nita Reddy - Thursday, February 16, 2017 9:27 AM

    ZZartin - Thursday, February 16, 2017 9:04 AM

    What do you plan to do with this random number?

    my store procedure ( exec SP_DEV_generateanynumber)

    When I execute the above SP I get 3@8 as output

    All I want to execute this 100 times in one attmept to get 100 random number

    Please note that you are dealing with stored procedures.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 16, 2017 at 11:25 am

    #1928915

    Change your proc to access a 'how many?' parameter and then use something like this:

    DECLARE @n INT = 10;

    SELECT TOP (@n) RAND(CHECKSUM(NEWID()))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n);

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 16, 2017 at 11:35 am

    #1928917

    Or my preferred format (so I know how many values I have)

    DECLARE @n INT = 10;

    SELECT TOP (@n) RAND(CHECKSUM(NEWID()))
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b(n);

  • February 16, 2017 at 12:11 pm

    #1928927

    Steve Jones - SSC Editor - Thursday, February 16, 2017 11:35 AM

    Or my preferred format (so I know how many values I have)

    DECLARE @n INT = 10;

    SELECT TOP (@n) RAND(CHECKSUM(NEWID()))
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b(n);

    I had to stare at that for at least 5 seconds before I spotted the difference(s)! Nice tweak, I prefer your format too.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 23, 2017 at 2:27 pm

    #1930051

    or how about just integers:

    DECLARE @n INT = 10;

    SELECT TOP (@n) convert(int,RAND(CHECKSUM(NEWID()))*@n)
    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) a(n)
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) b(n);

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

  • February 23, 2017 at 2:59 pm

    #1930054

    I can't say it's my idea. I'm not sure if this was from Dwain Camps or someone else, but having the numbers there means I can easily do some math. I've had a few cases where I wanted something like 25

    SELECT RAND(CHECKSUM(NEWID()))
    FROM (VALUES(0),(1),(2),(3),(4),(5)) a(n)  -- 5
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5)) b(n); -- * 5

    or 1000

    SELECT RAND(CHECKSUM(NEWID()))
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) a(n) -- 10
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) b(n); -- * 10
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) c(n); -- * 10

    and having the numbers listed there helps. Note, I have sometimes moved to the 0 at the end as well.

  • February 23, 2017 at 3:16 pm

    #1930057

    Nita Reddy - Thursday, February 16, 2017 8:57 AM

    I have a store Procedure which generate output as one random number

    I need to execute 100 times how can I do this.

    Thanks
    Nita

    Post the stored procedure.  We may be able to help in more ways than 1.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2017 at 3:49 pm

    #1930065

    Jeff Moden - Thursday, February 23, 2017 3:16 PM

    Nita Reddy - Thursday, February 16, 2017 8:57 AM

    I have a store Procedure which generate output as one random number

    I need to execute 100 times how can I do this.

    Thanks
    Nita

    Post the stored procedure.  We may be able to help in more ways than 1.

    In addition, what do you want to do with the output of the procedure?  I mean other than just generate 100 random numbers.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • February 23, 2017 at 3:57 pm

    #1930066

    Steve Jones - SSC Editor - Thursday, February 23, 2017 2:59 PM

    I can't say it's my idea. I'm not sure if this was from Dwain Camps or someone else, but having the numbers there means I can easily do some math. I've had a few cases where I wanted something like 25

    SELECT RAND(CHECKSUM(NEWID()))
    FROM (VALUES(0),(1),(2),(3),(4),(5)) a(n)  -- 5
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5)) b(n); -- * 5

    or 1000

    SELECT RAND(CHECKSUM(NEWID()))
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) a(n) -- 10
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) b(n); -- * 10
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), (0)) c(n); -- * 10

    and having the numbers listed there helps. Note, I have sometimes moved to the 0 at the end as well.

    I usually use either a Tally Table or a Tally function instead of hard coding a given return size.  That way, I only need to change a parameter instead of having to change code every time someone changes the requirements.  Even if I weren't allowed to have a Tally Table or Tally function, I'd write the row generator as a set of cascading CTEs and still use a parameter.  IIRC, several people have written books for RedGate on the subject of bullet proofing your code and, maybe it's just me, I consider that a form of bullet proofing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 16 total)

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