Stored Procedure Parameters giving you grief in a multi-purpose procedure?

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!

7 thoughts on “Stored Procedure Parameters giving you grief in a multi-purpose procedure?

  1. I’ve written statements like this in other queries in the WHERE clause, and it seemed like the optimizer would do what I wanted, For example:
    WHERE m.lastname LIKE ‘%’+ISNULL(@LastName)+’%’
    ie, if LastName was null, it would optimize this out of the query (ie, I had same performance when I commented out that part of the WHERE clause as when it was left in but @LastName was blank)
    Is my assessment correct, or am I paying a recompilation penalty?

  2. William,
    using a Like ‘%’ + @fieldname does not mean it will not use the indexes it becomes a non SARGAble query and it will not be able to use an index seek. It may still use an index scan and scan the entire index. Which it depends on the situation if that is good or bad. Your hope is for an Index seek. This is what I understood from the queries. HTH

  3. Kimberly,

    What about a construct like:

    where (@lastname is null or (@lastname is not null and m.lastname LIKE @lastname).

    I’ve found that syntax to work as expected and perform well…

  4. How about this approach (credit to Alex Santantonio)? The syntax is certainly clean:

    WHERE m.lastname = ISNULL(@LastName, m.lastname)

  5. Question from an OLAP programmer:

    I’m maintaining an OLAP installation with several reports.
    Each report is implemented as a UD Function.
    Most report UD Functions include calls to a central UD Function.
    This central UD Function takes as input a big number of SARGs.

    I was hoping to optimize this central UD Function by rewriting it using dynamic T-SQL.
    However, I ran into a couple of problems:

    It seems I am unable to use EXEC or sp_executesql in the central UD Function.
    INSERT / EXEC syntax is only valid with Temporary Tables, and I can only create Table Variables in my UD Function.

    Further, it seems hard to rewrite the central UD Function only as a stored procedure – the same problem would occur each time I wanted to call the new Procedure from a report UD Function.

    So, have you got any ideas how to solve this?

    Should all reports be rewritten to use Stored Procedures?
    I read SQL Server Bible 2000, and got the impression UD Functions were sorta superior if you were commited to the MS database.

    Am I simply missing some basic point somewhere :-)

    Thanks for any input!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.