CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
PriceCheaperton
PriceCheaperton

Reputation: 5379

Cannot return results from stored procedure in SQL SERVER

I have the following stored procedure, but it doesnt seem to return me the 6 numbers:

CREATE PROCEDURE GenerateRandomNumbers
   (  
   @StartNumber        tinyint,   
   @EndNumber          tinyint,   
   @QuantityToOutput   tinyint, 
   @AllowDuplicates    bit     --0 = No, 1 = Yes (Of Course)
   )

AS
BEGIN
   --Make sure that the input params are valid  
   --@StartNumber must be less than @EndNumber

   IF @StartNumber >= @EndNumber
       PRINT 'You turkey!  @StartNumber has to be LESS THAN @EndNumber!'
       RETURN

   --@EndNumber - @StartNumber must be >= @QuantityToOutput IF @AllowDuplicates = 0

   IF @AllowDuplicates = 0 AND @EndNumber - @StartNumber <= @QuantityToOutput

       PRINT 'Not enough numbers in the range to satisfy your OUTPUT and DUPLICATE parameter settings!'

       RETURN
   --We're good to go if we get this far.

   --Create a table to hold the "boundary points" of each number.

   CREATE TABLE #NumberBoundaries
       (Number      tinyint, UpperLimit  decimal (15,10)   )
   INSERT  #NumberBoundaries(Number, UpperLimit)
   SELECT  sv.number, 
  --The line below implements the "end point" math I spoke about earlier.    
  (1.0000000/(@EndNumber + 1 - @StartNumber) * (sv.Number + 1 - @StartNumber))  AS UpperLimit
   FROM    master..spt_values sv
   WHERE   sv.Number <= @EndNumber  AND     sv.Number >= @StartNumber ANDsv.type = 'P'


   --This holds the generated numbers until we have enough to return a result set

   CREATE TABLE #NumbersToOutput(Number   tinyint NOT NULL )


   --Loop until we're dizzy

   WHILE (SELECT COUNT(*) FROM #NumbersToOutput) < @QuantityToOutput

   BEGIN

       --This CTE fetches the bucket in which a given execution of RAND() falls.

       WITH        MyNumber (Number)

       AS          (SELECT TOP 1 nb.Number

       FROM        #NumberBoundaries nb

       WHERE       RAND() < nb.UpperLimit

       ORDER BY nb.Number ASC)


       INSERT      #NumbersToOutput

       SELECT      mn.Number

       FROM        MyNumber mn

       --The line below allows us to either permit duplicates or check for them.

       WHERE       @AllowDuplicates = 1
                   OR  mn.Number NOT IN (SELECT Number FROM #NumbersToOutput) 
   END
   --Return our numbers

   SELECT      Number

   FROM        #NumbersToOutput

   ORDER BY 1

END

I use the following to call the stored proc:

EXEC GenerateRandomNumbers 1, 49, 6, 0

Upvotes: 1

Views: 289

Answers (2)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Put BEGIN and END for IF statement. The REturn gets executed always now

IF @StartNumber >= @EndNumber
   BEGIN   --add this
       PRINT 'You turkey!  @StartNumber has to be LESS THAN @EndNumber!'
       RETURN
   END --add this
   --@EndNumber - @StartNumber must be >= @QuantityToOutput IF @AllowDuplicates = 0

   IF @AllowDuplicates = 0 AND @EndNumber - @StartNumber <= @QuantityToOutput
   BEGIN  --add this
       PRINT 'Not enough numbers in the range to satisfy your OUTPUT and DUPLICATE parameter settings!'
       RETURN
  END --add this

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453940

  IF @StartNumber >= @EndNumber
       PRINT 'You turkey!  @StartNumber has to be LESS THAN @EndNumber!'
       RETURN

should be 

  IF @StartNumber >= @EndNumber
       BEGIN
       PRINT 'You turkey!  @StartNumber has to be LESS THAN @EndNumber!'
       RETURN
       END

Or RETURN will always end up being executed. And a similar issue with the next IF exists too.

Without a BEGIN ... END the IF only applies to the next statement (PRINT).

Upvotes: 3

Related Questions