none
Arrays in MSSql Stored Procedures

    Question

  • Hi,

    thanks in Advance,

    I want to write a stored procedure which uses Arrays and random number concept.

    CREATE PROCEDURE GetCID
        @UID int  
    as
    BEGIN
        select username from tbl_users where UID = @UID;  
    END

    In above procedure, i will get multiple usernames as a result.

    select ceiling(10*RAND()) as index

    by using above random generation number, i want to fetch the username[index] from the result.

    Please help me on this.
    AnilJayanti
    Thursday, August 25, 2011 12:14 PM

Answers

  • Use the NEWID() function to get your random row,e.g.

    CREATE PROCEDURE GetCID @UID INT
    AS
        SET NOCOUNT ON ;
         SELECT TOP 1
                *
        FROM    ( SELECT    username
                  FROM      tbl_users
                  WHERE     UID = @UID
                )
        ORDER BY NEWID() ;
    • Proposed as answer by Naomi NModerator Thursday, August 25, 2011 2:03 PM
    • Marked as answer by aniljayanti Friday, August 26, 2011 4:15 AM
    Thursday, August 25, 2011 12:15 PM
  • Is your intent to select a single random row for the multiple rows returned by the query?  In that case, I don't see the need to use an "array" for this purpose.  Instead try:

     

    SELECT TOP (1) username 
    FROM dbo.tbl_users
    WHERE UID = @UID
    ORDER BY NEWID(); 
    

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Thursday, August 25, 2011 2:04 PM
    • Marked as answer by aniljayanti Friday, August 26, 2011 4:15 AM
    Thursday, August 25, 2011 12:20 PM

All replies

  • Hi,

    thanks in Advance,

    I want to write a stored procedure which uses Arrays and random number concept.

    CREATE PROCEDURE GetCID
        @UID int   
    as
    BEGIN
        select username from tbl_users where UID = @UID;   
    END

    In above procedure, i will get multiple usernames as a result.

    select ceiling(10*RAND()) as index

    by using above random generation number, i want to fetch the username[index] from the result.

    Please help me on this.


    AnilJayanti
    Thursday, August 25, 2011 12:12 PM
  • Use the NEWID() function to get your random row,e.g.

    CREATE PROCEDURE GetCID @UID INT
    AS
        SET NOCOUNT ON ;
         SELECT TOP 1
                *
        FROM    ( SELECT    username
                  FROM      tbl_users
                  WHERE     UID = @UID
                )
        ORDER BY NEWID() ;
    • Proposed as answer by Naomi NModerator Thursday, August 25, 2011 2:03 PM
    • Marked as answer by aniljayanti Friday, August 26, 2011 4:15 AM
    Thursday, August 25, 2011 12:15 PM
  • Is your intent to select a single random row for the multiple rows returned by the query?  In that case, I don't see the need to use an "array" for this purpose.  Instead try:

     

    SELECT TOP (1) username 
    FROM dbo.tbl_users
    WHERE UID = @UID
    ORDER BY NEWID(); 
    

     

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NModerator Thursday, August 25, 2011 2:04 PM
    • Marked as answer by aniljayanti Friday, August 26, 2011 4:15 AM
    Thursday, August 25, 2011 12:20 PM
  • I see you posted this same question twice.  Please continue discussion in your other thread to avoid duplication of effort on all parties involved.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, August 25, 2011 12:22 PM