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

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 <ckspot-t... (AT) yahoo (DOT) com> wrote:

Cindy (ckspot-temp (AT) yahoo (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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 <esq... (AT) sommarskog (DOT) se> wrote: