-- SCRIPT CREATED BY : MAURO C. PICHILIANI
-- DATE : 5/02/2001
-- STORED PROCEDURE PROC_RANDOM AND EXAMPLES OF USE
-----------------------------------------------------

-- DROP THE PROCEDURE 
DROP PROCEDURE PROC_RANDOM

CREATE PROCEDURE PROC_RANDOM @VEZES INT , @TABELA CHAR(50) , @CAMPO CHAR(50) , @REPETIR CHAR(1) = "S"
AS 

-- DECLARATION OF VARIABLES 
DECLARE @CONTADOR INT , @VAL INT , @AUX CHAR(10)
DECLARE @TOTAL INT , @VALOR INT
DECLARE @NAME CHAR(50)

-- NO MESSAGES
SET NOCOUNT ON


-- CHECK FOR THE TABLE CALLED 'RETORNO'
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[RETORNO]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
	DROP TABLE [DBO].[RETORNO]

-- CREATES THE TABLE

CREATE TABLE RETORNO
(
	CAMPO  CHAR(50)
)


-- IF THE USER DON'T WANT SAME RESULTS , CREATE A TEMPORARY TABLE 
IF @REPETIR = "N"
BEGIN
	CREATE TABLE #ALE
	(
		COD INT
	)
END

-- GET THE TOTAL NUMBER OF RECORDS , USING THE SYSINDEXES TABLE 
SELECT @TOTAL = ROWS FROM SYSINDEXES
WHERE ID = object_ID(@TABELA)
AND INDID < 2

-- IF THERE IS A TABLE....
IF @TOTAL IS NOT NULL
BEGIN
	
	-- CLEAN THE COUNTER
	SELECT @CONTADOR = 0
	
        -- DO A LOOP SEVERAL TIMES TO GET THE NUMBER , AND RECORDS , OF RANDOM RECORDS THAT THE USER WANT
	WHILE @CONTADOR <> @VEZES
	BEGIN

		-- INCREASE THE COUNTER
		SELECT @CONTADOR = @CONTADOR + 1
	
		-- GET A RANDOM NUMBER USING THE DATE....
		SELECT  @VAL = REVERSE((DATEPART(mm, GETDATE()) * 100000 )
	           + (DATEPART(ss, GETDATE()) * 1000 )
	           + DATEPART(ms, GETDATE()))
	
		SELECT @AUX = CONVERT(CHAR,@VAL * ( @CONTADOR )  )
		
		SELECT @AUX = REVERSE(SUBSTRING(@AUX,3,5))

		SELECT @VALOR = (CONVERT(INT,@AUX)%@TOTAL)+1
	

                -- IF THE USER DON'T WANT TO REPEAT DATA...
		IF @REPETIR = "N"
		BEGIN
			-- CHECK FOR THE USE OF THIS NUMBER
			IF NOT EXISTS(SELECT COD FROM #ALE WHERE COD = @VALOR)
			BEGIN
				
				-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
				EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR  SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )

				OPEN CUR_MEU

				-- GO TO THE LAST RECORD 
				FETCH LAST FROM CUR_MEU INTO @NAME

				-- INSERT THE DATA IN THE RETURN TABLE
				INSERT INTO RETORNO VALUES(@NAME)

				CLOSE CUR_MEU

				DEALLOCATE CUR_MEU
				
				-- STORE THE RANDOM NUMBER IN A TABLE TO AVOID THE USE OF THIS NUMBER AGAIN
				INSERT INTO #ALE VALUES(@VALOR)
				
			END
			ELSE
			BEGIN
				-- IF THIS NUMBER WAS USED THEN DECREASE THE COUNTER AND TRY AGAIN...
				SELECT @CONTADOR = @CONTADOR - 1
			END
			
		END		
		ELSE
		BEGIN
			-- CREATE A DYNAMIC CURSOR WITH SOME RECORDS. THE LAST WILL BE THAT RANDOM
			EXECUTE("DECLARE CUR_MEU CURSOR SCROLL FOR  SELECT TOP " + @VALOR + " " + @CAMPO+ " FROM " +@TABELA )

			OPEN CUR_MEU

			-- GO TO THE LAST RECORD 
			FETCH LAST FROM CUR_MEU INTO @NAME

			-- INSERT THE DATA IN THE RETURN TABLE
			INSERT INTO RETORNO VALUES(@NAME)

			CLOSE CUR_MEU

			DEALLOCATE CUR_MEU
				
		END
		
	END
	
	SET NOCOUNT OFF
END

-- IF THE TABLE WAS CREATED , WE MUST DROP IT
IF @REPETIR = "N"
BEGIN
	DROP TABLE #ALE
END


-- RETURN TO THE USER ALL RANDOM RECORDS
SELECT CAMPO FROM RETORNO

-- DROP THE TABLE WITH THE RANDOM RECORDS
DROP TABLE RETORNO

GO

/* END OF THE STORED PROCEDURE */
	

/* ----------------------------------
USE OF THE STORED PROCEDURE:
1º ARGUMENT: HOW MUCH RANDOM RECORDS THE STORED PROCEDURE SHOULD RETURN
2º ARGUMENT: NAME OF THE TABLE WICH THE RANDOM RECORDS SHOULD RETURN
3º ARGUMENT: FIELD ( COLUMN ) OF THE TABLE. MUST BE A CHAR(50) OR LEAST. THIS CAN BE CHANGED
BY OTHERS DATATYPES WITH A FEW MODIFICATIONS

4º ARGUMENT:  "S"-> THE PROCEDURE CAN RETORN SAME RANDOM RECORDS
              "N"-> ALL RECORDS RETURNED SHOULD BE DIFFERENT

OBSERVATION: THIS FOLLOWING EXAMPLES ARE TESTED BASED ON THE TABLES OF THE NORTHWIND DATABSES 
CREATED BY THE INSTALATION PROGRAMAN OF SQL SERVER 7.0 AND 2000

*/


EXEC PROC_RANDOM 10,"PRODUCTS" , "PRODUCTNAME", "S"

EXEC PROC_RANDOM 10,"SUPPLIERS" , "COMPANYNAME"  , "N"

EXEC PROC_RANDOM 10,"SUPPLIERS" , "COMPANYNAME"  , "S"

EXEC PROC_RANDOM 10,"SUPPLIERS" , "CompanyName+contactName"  , "S"

EXEC PROC_RANDOM 10,"SUPPLIERS" , "CompanyName+contactName"  


/*****************************************************************************************/
/* END OF FILE */