Well… the performance ramifications are… not good!


Without seeing more of the proc I’d have to suggest that you avoid this like the plague! I think I can convince you with this simple example which shows why it’s better to have multiple procedures and limit the flexibility a bit OR use DSE = dynamic string execution (and unfortunately even recompilation issues don’t help – which I talked about here)…


And – if we go down the path of DSE I’m sure that will start a separate thread as far as “sql injection” issues/attacks and security (the user executing the procedure will need permission to directly execute the statement which is in the DSE string). But – there are a few ways to protect parameters in a stored procedure. In this procedure, the parameters will not allow “injection” because of the addition of the QUOTENAME function. This function is INVALUABLE for protecting isolated input values (which is another trick: isolate parameters if you can).


In the following “multi-purpose” procedure it becomes obvious (after testing) that ONLY DSE solves the performance problem. I could still argue that more than one procedure could be beneficial especially as it wouldn’t have the access requirements that this one has (remember – users would have to have permissions to the base table). BUT that’s a lot more work to maintain/call, etc. Regardless, one procedure with “all purpose parameters” is definitely NOT a good choice (without DSE). (And fyi – a future release might help with some of the permissions issues – that’s all I’ll say for now! :)


OK – so see the ProcedureParameters.sql (4.3 KB) procedure to play with this within the CreditSampleDB.zip (55.79 KB). You will need to modify some data to get rows to return to these queries; use the updates in the .sql script.



CREATE PROC dbo.GetMemberInfoParam
(
 @Lastname varchar(30) = NULL,
 @Firstname varchar(30) = NULL,
 @member_no int = NULL
)
AS
SET NOCOUNT ON


SELECT m.*
FROM dbo.member AS m
WHERE (m.lastname LIKE @lastname OR @lastname IS NULL)
 AND (m.member_no = @member_no OR @member_no IS NULL)
 AND (m.firstname LIKE @firstname OR @firstname IS NULL)
go


This next procedure uses dynamic string execution to build the exact and needed string. You could have built this from the client as well… and that would introduce other sql-injection issues as well. But – this is a simpe idea and I hope this helps.



CREATE PROC dbo.GetMemberInfoParamDSE
(
 @Lastname varchar(30) = NULL,
 @Firstname varchar(30) = NULL,
 @member_no int = NULL
)
AS
SET NOCOUNT ON


IF @LastName IS NULL AND @FirstName IS NULL AND @Member_no IS NULL
 RAISERROR (‘You must supply at least one parameter.’, 16, -1)


DECLARE @ExecStr varchar(1000)
  , @MemberNoStr varchar(100)


SELECT @ExecStr = ‘SELECT m.* FROM dbo.member AS m WHERE ‘


IF @LastName IS NOT NULL
 SELECT @Lastname = ‘m.lastname LIKE ‘ + QUOTENAME(@lastname, ””)
IF @FirstName IS NOT NULL
 SELECT @Firstname = ‘m.firstname LIKE ‘ + QUOTENAME(@firstname, ””)
IF @Member_no IS NOT NULL
 SELECT @MemberNoStr = ‘m.member_no = ‘ + convert(varchar(5), @member_no)


SELECT @ExecStr = @ExecStr + ISNULL(@LastName, ‘ ‘)
 +
 CASE
  WHEN @LastName IS NOT NULL AND @FirstName IS NOT NULL
   THEN ‘ AND ‘
  ELSE ‘ ‘
 END
 +
 ISNULL(@FirstName, ‘ ‘)
 +
 CASE
  WHEN (@LastName IS NOT NULL OR @FirstName IS NOT NULL)
     AND @MemberNoStr IS NOT NULL
   THEN ‘ AND ‘
  ELSE ‘ ‘
 END
 +
 ISNULL(@MemberNoStr, ‘ ‘)


EXEC(@ExecStr)
go


The first procedure generates a plan based on the MOST selective criteria (there’s more info in the script and you’ll see this in the showplan/statistics io output that I describe – also in the script). The second procedure generates a good plan for each and every execution (as expected). I think this is a good example of what you can do!


I’m going to put together one more entry to bring all of these points together as well. Stay tuned!


Have fun!