Dynamic SQL And NewID Function - Pulling Random Records

Jun 11, 2007

I'm trying to use the NEWID function in dynamic SQL and get an error
message Incorrect syntax near the keyword 'ORDER'. Looks like I can't
do an insert with an Order by clause.

Here's the code:
SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROM
FD__UR_Randoms '
SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +
@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDER
BY NEWID())'

execute sp_executesql @SQLString

My goal is to get a random percentage of records.

The full SP follows. In a nutshell - I pull a set of records from
FD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.
I need to retain the set of all records that COULD be eligible for
selection. Based on the count of those records, I calculate how many
need to be pulled - and then need to mark those records as "chosen".

I'd just as soon not use the TMP_UR_Randoms table - I went that route
because I ran into trouble with a #Tmp table in the above SQL.

Can anyone help with this? Thanks in advance.

Full SQL:

CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)

--Review type will fill using Crystal Parameter (setting defaults)
AS

/* 6.06.2007
UR Requirements:
(1) Initial 4-6 month review: 15% of eligible admissions
(eligible via days in program and not yet discharged) must be reviewed
4-6 months after admission. This review will be done monthly -
meaning we'll have a moving target of names (with overlaps) which
could be pulled from each month. (Minimum 5 records)
(2) Subsequent 6-12 month review: Out of those already reviewed
(in #1), we must review 25% of them (minimum of 5 records)
(3) Initial 6-12 month review: Exclude any included in 1 or 2 -
review 25% of admissions in program from 6-12 months (minimum 5)

*/

DECLARE @CodeRevType int
DECLARE @PriorRec int -- number of records already marked
eligible (in case user hits button more than once on same day for same
type of review)
DECLARE @CurrRec int --number of eligible admits
DECLARE @RequFiles int

DECLARE @SQLString nvarchar(1000)
DECLARE @RequFilesSt varchar(100)
DECLARE @CodeRevTypeSt char(1)

DECLARE @TodayNotime datetime
DECLARE @TodaySt varchar(10)


--strip the time off today

SELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)

--convert the review type to a code
Select @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then
1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'
then 3 END

--FD__UR_Randoms always gets filled when this is run (unless it was
previously run)
--Check to see if the review was already pulled for this record

SELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms where
SelectType = @CodeRevType and SelectDate = @TodayNotime)



If @PriorRec 0 GOTO ENDThis

--************************************STEP A: Populate FD__UR_Randoms
table with records that are candidates for review
************************


If @CodeRevType = 1
BEGIN

INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) 119)
AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)
AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randoms
where RecordChosen = 'T'))

END

If @CodeRevType = 2
--only want those that were selected in a batch 1 - in program 6-12
months; selected for first review
BEGIN

INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) 211)
AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)
AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randoms
where SelectType = 1 AND RecordChosen
= 'T'))

END

If @CodeRevType = 3
--only want those that were not in batch 1 or 2 - in program 6 to 12
months
BEGIN

INSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,
SelectType,RecordChosen)
(SELECT pa.OP__DOCID, pa.Client_ID,
Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'
FROM dbo.FD__RESTART_PROG_ADMIT pa
Inner join FD__Client c
On pa.Client_ID = c.Client_ID
WHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)
AND
(DATEDIFF(d, Date_Admission, GETDATE()) 211)
AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)
AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randoms
where SelectType < 3 AND RecordChosen
= 'T'))

END

SELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms where
SelectType = @CodeRevType and SelectDate = @TodayNoTime)

--*************************************STEP B Pick the necessary
percentage **************************************

--if code type = 1, 15% otherwise 25%

If @CodeRevType = 1
BEGIN
SELECT @RequFiles = (@CurrRec * .15)
END
ELSE

BEGIN
SELECT @RequFiles = (@CurrRec * .25)
END

--make sure we have at least 5
If @RequFiles < 5
BEGIN
SELECT @RequFiles = 5
End

--*************************************STEP C Randomly select that
many files**************************************
--convert all variables to strings

SELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)
SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)
SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)

SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,
Client_ID, SelectDate, SelectType,RecordChosen)'
SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + '
Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROM
FD__UR_Randoms '
SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +
@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDER
BY NEWID())'

print @SQLString

execute sp_executesql @SQLString
SELECT * FROM TMP_UR_Randoms

/*
--This select statement gives me what i want but I need to somehow
mark these records and/or move this subset into the temp table
Select Top @RequFiles
FROM FD__UR_Randoms
WHERE SelectType = @CodeRevType and SelectDate =
Convert(varchar(10),GetDate(),101))
ORDER BY NewID()

*/


ENDTHIS:
GO

View 3 Replies


ADVERTISEMENT

Distinct Random Rows Using NewID()

May 26, 2008

I have 2 tables, Artists and Artworks.
I have a query:

SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()

This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,

Paolo

View 8 Replies View Related

SQL Server 2012 :: Return Random Records In A Table-valued Function?

Dec 19, 2013

My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.

I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).

CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE

[Code] ....

But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:

Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.

I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...

The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)

but that's clearly not working. Is there a way to do this without resorting to a cursor?

View 9 Replies View Related

Returning Random Records And NOT Similar (random Questions)

Jul 20, 2005

Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke

View 1 Replies View Related

Dynamic Function To Return Number Of Records In Table

Aug 5, 2014

I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.

CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT

[Code] .....

View 1 Replies View Related

Problem Updating Existing Records With Newid()

Apr 23, 2008

I currently have a table called stores.  I've just added a uniqueidentifier column called store_guid to the stores table.  The table currently has about 500 records in it and now i'm trying to set each store_guid = to a newid().  I've tried using  UPDATE stores SET store_guid = newid()  .   however, that doesn't work because i think it's trying to set each record equal to the same guid using that approach.  All i need to do is fill in my new column with new guids.  any ideas?
 
Thanks in advance,
 

View 3 Replies View Related

Pulling Unique Records With Max Dates

Mar 5, 2006

I am trying to pull only those records with a maximum upload date for a file upload log.

This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.

The field names are:
SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.

So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...

Thanks Much in advance

View 4 Replies View Related

Access Query Help Pulling Miltiple Records

Jan 25, 2007

Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!

<cfquery name="formWait" datasource="#datasource#">
SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName,
formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus,
formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as
ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status
FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2 where teacherStatus = 2 and appraiserStatus = 1 and teacherID=#id#
</cfquery>

View 7 Replies View Related

SQL Server 2008 :: Query Pulling Too Many Records

Feb 11, 2015

My query is pulling to many records becuase of the last join. This table can have multiple registration files for a computer. I just want the latest one or last one insert which is based on dttRegistration. I thought a top 1 select and order by would do it, but now returns no computer names.

Selectdr.intRecId,
c.strCategory,
mm.strManufacturer,
dm.strMake, m.strModel,

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: Pulling Incorrect Records Using Date Range In Where Clause

Apr 22, 2014

I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:

select *
from dbo.ACCTING_TRANSACTION_hISTORY
where ath_postype = 'NTC' or ath_postype='NTD' and

ath_postdate >= '2013-01-01 00:00:00' and
ath_postdate <= '2013-01-05 23:59:59'

I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.

View 9 Replies View Related

Random Function

Jan 25, 2006

Hi all,

I have a table with a multiple fields. I want to create a query that it will show me one dataset according to a random numder the system choose...

For istanse if it choose the number 13 it will show all the records conserning that id

I used the following query, but of course it is wrong

select * from famous_queries
where id = rand(id)


Could someone help me with this please?

View 15 Replies View Related

Random Records

Nov 16, 1999

Hi,

I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark

SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand
FROM mytable
ORDER BY recrand

View 3 Replies View Related

Get Random Records..how ? Please

Feb 10, 2007

Hi every one.How can I get random 4 records in my table.
Example I want to get top 4 random records
Select top 4 from tbl_Products.....?
Thanks in appreciate

View 12 Replies View Related

Selecting Random Records

May 11, 2008

hi again,
i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure??
thanks

View 3 Replies View Related

Random Records Selection

Mar 13, 2004

hi im creating 2 random numbers, matching one of them (i) to the QuestionsNo column in my database, and then making it display the whole record. the code below generate 2 random numbers but i cannot get the rest of it to work. i need it to match the random number to the QuestionNo field in my database and display the whole record. can anyone help me and tell me where im going wrong please????

Dim i,j as integer
Randomize
i = CInt(Int((10 * Rnd()) + 1))
j = CInt(Int((10 * Rnd()) + 1))
example.InnerHtml = "Random Number : " & i
example1.InnerHtml = "Random Number : " & j



strSQL = "SELECT * FROM Table1 WHERE QuestionNo = " & i &""


Message.text=strSQL.ToString()

View 1 Replies View Related

Random Records Script

Jun 5, 2001

Hello,

I downloded the random record script from swynk site and when I run It gives me error msg

Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.

Can anyone guide me

I've copied the script

Thanks.


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 */

View 1 Replies View Related

Pick Random Records

Jun 25, 2002

I need a way to select records at random from a SQL Server 2000 table. If the record I've selected at random meets a few simple conditions (column X <> 'A' and column Y == 'Z') then return this record and get the next. Ideally I'd be able to do this in a stored proc where I'd call it with:

parameter 1: table name
parameter 2: number of random records to get
parameter 3: value for condition 1
parameter 4: value for condition 2

Any help will be greatly appreciated!

Patrick

View 4 Replies View Related

Random Records Script

Jun 5, 2001

Hello,

I downloded the random record script from swynk site and when I run It gives me error msg

Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 73
Line 73: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.
Server: Msg 170, Level 15, State 1, Procedure PROC_RANDOM, Line 101
Line 101: Incorrect syntax near 'DECLARE CUR_MEU CURSOR SCROLL FOR SELECT TOP '.

Can anyone guide me

I've copied the script

Thanks.


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 */

View 2 Replies View Related

Insert Random Records..HELP!

Jul 1, 2004

Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and last 5 digits should be randomly choosed (should be like this: +381randomno1randomno2.. etc.(example: +38156465, where '+' sign makes it char type and digits after +381 are randomly choosed. :confused: Anyone knows how to solve this....PLEASE?

View 3 Replies View Related

Selecting 10 Random Records

Mar 1, 2004

How would I create a statement that would select 10 random records from the SQL DB?

View 5 Replies View Related

Select 10 Random Records

Apr 22, 2008

Hello,

Is it possible to select 10 random records from a table?

Thanks,
Miguel

View 1 Replies View Related

10 Random Records, 1 From Each Category

Dec 1, 2006

I'm not sure if this is a completely dumb question, but please humor me:) I have a table of records, called Records, each of which has aCategory_ID that places it in a specific category; the details of thecategories are stored in another table called Category. What I need todo is retrieve a recordset that contains one record from each category,but where the records that are retrieved are random. I know how toretrieve one or more random records using "order by NewID()", but havenot been able to work out how to get one random record from eachcategory.Any assistance in this puzzler will be HUGELY appreciated!ThanksFEB

View 7 Replies View Related

Returning 30% Random Records

Sep 27, 2007

HI,

If i have the following data




Code Block

Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);

Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');





How can i return 30% random requestid's from this table?

thanks.

View 9 Replies View Related

Cant Work Out This SQL To Return 4 Random Records

Feb 10, 2007

I've got a product table in SQL 2000 that contains say these rows:
ProductID intProductName varcharIsSpecial bit
I want to always return 4 random specials from a query and can do this fine by using:
 SELECT TOP 4 ProductID,ProductNameFROM Products WHERE IsSpecial = 1ORDER BY NEWID()
This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.
What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products  returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?
Is this possible?
Thanks

View 2 Replies View Related

Select Random Records From MS SQL Table?

Aug 26, 2005

Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards

View 3 Replies View Related

Selecting Records By Random Order

Sep 22, 2000

Hello,

I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.

SELECT * FROM QuestionMaster.

I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.

SELECT Convert(int,RAND()*97)

Thanks in a million.

Sarika

View 1 Replies View Related

Query Random Records With Sum Creteria

Sep 7, 2005

Dear All ...
im new memmber in this great forum ..
i need help ...
i have Table like :
ID Subject Points
---- ------- --------
1 1 10
2 1 20
3 2 10
4 3 5
5 1 15
6 2 10
7 3 10
8 1 20

i need to select random records from table where Subject = 1 and Total Sum for selected records Points = 25

any one can help me ..
thanks

View 2 Replies View Related

Random Loop Through Table Without Using The Same Records Over Again.

Jul 31, 2007

Hello,

Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.

View 5 Replies View Related

How To Assign Random Number To One Column For 500 Records?

Aug 4, 2005

I try to set up a testing sample table which contain one  integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!

View 1 Replies View Related

How To Make 2 Columns In Datagrid With Random Records?

Aug 20, 2005

Hi

I want to make random record from both columns

Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"

but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"

As you see i want both columns to be random placed..

Please help me...

View 1 Replies View Related

How To Select Random Records From Database (advanced)

Mar 12, 2006

hello i have a problem and i dont know if it can fixed or not i'm using asp.net 2 and sqlserver 2005 i have a table named questions in the database and that table has 5 columns questionid,question,answer1,answer2,answer3 every questions has 3 answers 1 right and the other 2 are wrong what i want to do is select 10 random questions and their right and wrong answers show in my asp.net page as application the answers will be in radio buttons so is that can be done or it's not possible.thanks all

View 17 Replies View Related

SQL Union Problems When Trying To Retrieve Random Records

Jun 6, 2006

I have a situation where i am trying to run two query's and retrieveone record from each query, then using Union combine them into onerecordset. The First Query is strait forward and should just returnthe matching record however the second query needs to find a randomrecord and return it. The union is causing me some trouble. It seemsthat any method i try to find a random record just causes an error.Here is an example of a query that's causing the problem.--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by newID()--------------------------------------------------------------------------------------------------------I have also tried the following which does not provide an error but icannot find a way only to return one record from the second query--------------------------------------------------------------------------------------------------------declare @variable1 smallintdeclare @variable2 intset @variable1 = 10002set @variable2 = 1001211720select newID() as colID, col1, col2, col3, col4, col5, col6from table1where col1 = @variable1 and col2 = @variable2unionselect newID() as colID, col1, col2, col3, col4, col5, col6from table2where col2 = @variable2order by colIDIf anyone has any ideas, fixes, or thoughts i would appreciate them...thanks in advance...FYI i am fairly inexperienced in SQL so please feel free to let me knowif you need more of an explination.

View 9 Replies View Related

Reporting Services :: Select 10 Random Records From Past 30 Days For Each User

May 18, 2015

I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved