Set RowCount issue in query containing function tables

Dr_X

I have the query below which contains 2 functions which return tables, fn_SamplesUserProducts & fn_SamplesUserOffices. The query itself I want to set the rowcount to say 10. However, my functions will return more than 10 rows and obviously they don't due to the set RowCount. I tried to set the RowCount in the function but an error occurs when attempting to compile: Invalid use of 'UNKNOWN TOKEN' within a function. Is there a work-around? The query is the first part of a complex cross-tab w/o reporting services installed.

SELECT s.SampleID,
convert(CHAR(10), s.PullDate, 111) + ' ' + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A' + rtrim(convert(varchar(8), S.SampleID))))) as ColumnName,
s.PullDate,
s.TripNumber,
s.Quantity,
convert(CHAR(10), s.PullDate, 112) + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A'))) + rtrim(convert(varchar(8), S.SampleID))
FROM tblSamplesSample s,
tblSamplesSampleTerminalPortWellLocation stp,
fn_SamplesUserProducts(@UID) fnp,
fn_SamplesUserOffices(@UID) fno
WHERE S.SampleID *= stp.SampleID
AND (S.Deleted = 0 or S.Deleted IS NULL)
AND (stp.Deleted = 0 or stp.Deleted IS NULL)
AND fnp.ProductID = s.ProductID
AND fno.OfficeID = s.OfficeID
AND ((@DateRangeBegin IS Null AND @DateRangeEnd IS Null)
OR PullDate BETWEEN @DateRangeBegin AND @DateRangeEnd
)
AND (@TerminalPortWellLocationID IS Null
OR stp.TerminalPortWellLocationID = @TerminalPortWellLocationID
)
AND (@CustomerCompanyID IS NULL
OR s.CustomerCompanyID = @CustomerCompanyID
)
AND (@ProductID IS NULL
OR S.ProductID = @ProductID
)
AND (@CustomerProductReportID IS NULL
OR s.CustomerProductReportID = @CustomerProductReportID
)
AND (@DomesticInternational IS NULL
OR s.DomesticInternational = @DomesticInternational
)
AND S.StatusID = 1 --Finalized
GROUP BY S.SampleID, S.TripNumber, S.PullDate, s.Quantity
HAVING Count(stp.TerminalPortWellLocationID) = 1
ORDER BY s.PullDate DESC, TripNumber ASC, s.SampleID

Thanks, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)

Dr_X

I declared the following table variables and used them in FROM clause as a work-around. The table variables are populated prior to the SET RowCount command. However, is there a way around doing it this way?

DECLARE @SamplesUserProducts TABLE(ProductID int)
DECLARE	@SamplesUserOffices TABLE(OfficeID int)					

INSERT INTO @SamplesUserProducts SELECT ProductID FROM fn\_SamplesUserProducts(592)
INSERT INTO @SamplesUserOffices SELECT OfficeID FROM fn\_SamplesUserOffices(592)

Thanks, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)