Gah...
Just realized that that isn't going to work...
Because I'm actually using the TOP inside a subquery...
So it's
Select * from
Table1,
LEFT OUTER JOIN Table2 ON Table1.id =Table2.id
Where
Table1.id IN
(
Select top @total
Table1.id
Order by
Table1.date
)
Because I want @total records of table1, but I also want all the stuff
included with table2...
Ahhh.. the plot thicks...
TIA
Mark
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark M
Sent: Friday, 4 June 2004 8:29 PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: [OT] MS SQL Stored Proc Question
Thanks for your help guys!
I'll give it a shot!
Mark
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Thornton
Sent: Friday, 4 June 2004 4:39 PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: [OT] MS SQL Stored Proc Question
Hi,
Use
SET ROWCOUNT @PARAMETER
eg
create procedure Test
(
@PARAMETER int
)
as
SET ROWCOUNT @PARAMETER
select * from wherever
-- EXECUTE Test 10, will return 10 records.
go
cool huh? :-)
set rowcount 0 will of course return ALL records.
Have a good weekend all!.
Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter Area Health Service
Phone +61 2 49214193
Fax +61 2 49214191
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 4/06/2004 4:20:04 pm >>>
Hey guys -
I want to have a stored proc that returns back the top 'n' number of
items that I ask for (rather than
hardcoding the value).
i.e.
--
create procedure topResults
@total numeric
select top @total
*
from
myTable
RETURN
--
However, it really doesn't like the 'top @total' 'incorrect syntax' is
all I get.
Any way I can do this without resorting to dynamic SQL (either stored
proc, or CF side)
Cheers,
Mark
------------------------------------------------------------------
[EMAIL PROTECTED]
ICQ: 3094740
---
You are currently subscribed to cfaussie as:
[EMAIL PROTECTED]
To unsubscribe send a blank email to
[EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
