Variable Parameters - Which Approach.

ASP.NET / VB.NET
SQL Server 7.0

Our client has insisted that we change our established practice of
building SQL in-line and move it all to SPROCs. Not a problem for 80%
of the app. However, we have a number of Search/Filter forms that
contain a number of controls to allow more or less focused search
criteria to be built up.

I have a class that does this quite nicely, examining all controls for
valid input and, if found, concatenated onto the end of what becomes a
"WHERE" clause. Once all is done the "WHERE" clause is appended to a
SQL Statement and passed to an ExecuteReader function. Only at
runtime is it known how many, and which, controls will be used in the
filter.

I can think of two methods of approaching this variable parameter list
which will be required if moving to SPROCs.

METHOD 1.
Create a SPROC where ALL the possible parameters are used. For
example:

CREATE PROC stpSearchContactsUsingContactRef
@ContactRef varchar(50),
@Status varchar(50),
@IgnoreDate int,
@Date1 smalldatetime,
@Date2 smalldatetime
AS

SELECT
tblContactSources.*
FROM
tblContacts
WHERE
(tblContacts.fldContactID LIKE @ContactRef) AND
(tblStatuses.fldStatus LIKE @Status) AND
((tblContacts.fldDateCreated >= @Date1) AND
(tblContacts.fldDateCreated <= @Date2) OR @IgnoreDate = 0)
GO

If either of the parameters @ContactRef and @Status is empty, the code
that retrieves the filter values passes in the value '%', otherwise it
will pass in the user's choice. If the user wants not to search on the
date range, then @Date1 and @Date2 are passed in with '' and
@IgnoreDate is set to 0.

METHOD 2.
Create a SPROC which consists of SQL body as a string, with the
"WHERE" clause passed in as a string parameter, and both EXEC'd -

CREATE PROCEDURE stpTSRGetFilteredTSR
@WhereClause as varchar(3999)

AS

DECLARE @Body as varchar(3999)
DECLARE @OrderBy as varchar(3999)

SET @Body = 'SET CONCAT_NULL_YIELDS_NULL OFF
SELECT TOP 100 PERCENT
tblTSRs.fldTSRID, tblTSRs.fldFaultDescription,
tblTSRs.fldDateEntered, tblTSRPriorities.fldTSRPriority,
tblTSRs.fldTSRPriorityID, tblVehicles.fldRegistrationNumber,
tblVehicles.fldVIN, tblTSRStatuses.fldTSRStatus,
tblTSRs.fldTSRStatusID, tblTSRs.fldDealerID, tblTSRs.fldEmergency,
tblCustomers.fldFirstName + '' '' + tblCustomers.fldSurname AS
fldCustomerName
FROM
tblContactReasons
INNER JOIN tblContacts ON tblContactReasons.fldContactID =
tblContacts.fldContactID
INNER JOIN tblCustomers ON tblContactReasons.fldRegardingID =
tblCustomers.fldCustomerID
INNER JOIN tblVehicles ON tblContactReasons.fldVehicleID =
tblVehicles.fldVehicleID
RIGHT OUTER JOIN tblTSRs WITH (NOLOCK)
LEFT OUTER JOIN tblTSRStatuses ON tblTSRs.fldTSRStatusID =
tblTSRStatuses.fldTSRStatusID
LEFT OUTER JOIN tblTSRPriorities ON tblTSRs.fldTSRPriorityID =
tblTSRPriorities.fldTSRPriorityID ON tblContacts.fldContactID =
tblTSRs.fldContactID
WHERE (GETDATE() <= DATEADD(week, 2, tblTSRs.fldDateEntered))'

SET @OrderBy = 'ORDER BY tblTSRs.fldDateEntered'

SET @Body = @Body + @WhereClause + @OrderBy

EXEC (@Body)
GO

In this example, the @WhereClause parameter might typically be:
' AND (fldDealerID = 9) AND (fldTSRStatusID = 1) '

My question is, is there anything to recommend one method over the
other?

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk