Dynamic SQL and NewID function - pulling random records

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

Re: Dynamic SQL and NewID function - pulling random records

So sorry - something about typing up the request helped me think of a
different solution -

I changed the SQL to
SELECT @SQLString = N'UPDATE FD__UR_Randoms SET RecordChosen = ''' +
'T' + ''''
SELECT @SQLString = @SQLString + N'WHERE SelectDate = ''' + @TodaySt
+ '''' + ' AND SelectType = 1 AND Admit_DOCID IN '
SELECT @SQLString = @SQLString + N' (SELECT TOP 12 Admit_DOCID FROM
FD__UR_Randoms ORDER BY NEWID())'

Does the trick nicely and I can get rid of the temp table!!


On Jun 11, 4:31 pm, Cindy wrote:
> 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

Re: Dynamic SQL and NewID function - pulling random records

Cindy (ckspot-temp@yahoo.com) writes:
> So sorry - something about typing up the request helped me think of a
> different solution -
>
> I changed the SQL to
> SELECT @SQLString = N'UPDATE FD__UR_Randoms SET RecordChosen = ''' +
> 'T' + ''''
> SELECT @SQLString = @SQLString + N'WHERE SelectDate = ''' + @TodaySt
> + '''' + ' AND SelectType = 1 AND Admit_DOCID IN '
> SELECT @SQLString = @SQLString + N' (SELECT TOP 12 Admit_DOCID FROM
> FD__UR_Randoms ORDER BY NEWID())'

Don't interpolate the values into the query string, but use parameters
instead. This saves you from being entangled in a mess of quotes, and
saves you from a lot of other problems as well.

See here for details:
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

....and if you are on SQL 2005, you can use SELECT TOP(@var) in which
cases there is no need for dynamic SQL at all, as far as I can see.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Dynamic SQL and NewID function - pulling random records

As it turns out, the SelectType, the SelectDate, and the Top XX are
all variables - I posted a shortened version in the 'solution'... I
think the Top XX is the biggest one that makes me have to go for
dynamic SQL.

This isn't going to be run that often (a couple times a month), and
there aren't going to be that many records, so I'm hoping all will be
okay with it as is. Thanks though - and many thanks for the article
link. I started this out originally with parameters, and with the two
small examples in BOL I couldn't get it to work - it sounds like your
article was just what I needed, and will come in handy down the road.

Cindy


On Jun 11, 5:35 pm, Erland Sommarskog wrote:
> Cindy (ckspot-t...@yahoo.com) writes:
> > So sorry - something about typing up the request helped me think of a
> > different solution -
>
> > I changed the SQL to
> > SELECT @SQLString = N'UPDATE FD__UR_Randoms SET RecordChosen = ''' +
> > 'T' + ''''
> > SELECT @SQLString = @SQLString + N'WHERE SelectDate = ''' + @TodaySt
> > + '''' + ' AND SelectType = 1 AND Admit_DOCID IN '
> > SELECT @SQLString = @SQLString + N' (SELECT TOP 12 Admit_DOCID FROM
> > FD__UR_Randoms ORDER BY NEWID())'
>
> Don't interpolate the values into the query string, but use parameters
> instead. This saves you from being entangled in a mess of quotes, and
> saves you from a lot of other problems as well.
>
> See here for details:http://www.sommarskog.se/dynamic_sql.html#sp_execute sql
>
> ...and if you are on SQL 2005, you can use SELECT TOP(@var) in which
> cases there is no need for dynamic SQL at all, as far as I can see.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx