This is how I filter my report to include the data requested by the user via a parameter: ' Where gstcd_zip = ''32225'''. I use a view to return only the primary key of the table along with any fields that I allow the user to add for criteria such as name, site, city, etc. Then I link the view in the stored proc so it only returns the records requested, along with any other fields I want to include in the report. NOW, I don't know if it is any more efficient. But it work!
CREATE PROCEDURE procRptGuestList
@pchrWhere varchar(500) = NULL
AS
DECLARE @vSql nvarchar(1000)
--create the temporary table
CREATE TABLE #xtmpSelect (gstcn varchar(50))
--update with the records to be selected
Set @vSql = 'INSERT #xtmpSelect(gstcn) SELECT DISTINCT gstcn FROM viewRptSelGuestList '
Set @vSql = @vSql + @pchrWhere
EXEC sp_executesql @vSql
--return the recordset for the report
SELECT tblGuest.gstnm_last, tblGuest.gstnm_first, tblSite.stenm
FROM tblGuest
INNER JOIN tblSite
ON tblGuest.gstky_ste = tblSite.stecn
INNER JOIN #xtmpSelect
ON tblGuest.gstcn = #xtmpSelect.gstcn
ORDER BY tblGuest.gstnm_last, tblGuest.gstnm_first
GO
CREATE VIEW dbo.viewRptSelGuestList
AS
SELECT gstcn, gstky_ste, gstnm_last, gstdt, gstcd_zip
FROM tblGuest
IP Logged
