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?”
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?
You should look at http://www.sommarskog.se/dyn-search.html. Erland Sommarskog provides a good guide on the various methods of dynamic SQL
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
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…
How about this approach (credit to Alex Santantonio)? The syntax is certainly clean:
WHERE m.lastname = ISNULL(@LastName, m.lastname)
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!