Number of Records to Retreive. 1

[fbermudez]

Is there a way to write a stored procedure using SQL Query analyzer for a Crystal Report that will prompt for the user for the number of records to retreive?

In the past we have gotten away by using

Select top 10... but they would like more flexibility.

The end-user would like the flexibility to retreive the top 3, top 25, top what-ever number using a parameter in Crystal 9?

Any ideas / suggestions are greatly appreciated.

 

[vidru]

Yep, you can do it with dynamic SQL. Here's an example using the SQL Server pubs database:
[tt]
CREATE PROC spDynamicTest
@TopN INT
AS
DECLARE
@SQL NVARCHAR(200)

SET @SQL = 'SELECT TOP ' + STR(@TopN) + ' au_id, au_lname, au_fname ' + CHAR(13) +
'FROM authors ' + CHAR(13) +
'ORDER BY au_lname DESC'

EXEC sp_executesql @SQL
[/tt]
-dave