Well… the performance ramifications are… not good!<\/P>
\n
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 <\/EM>here<\/EM><\/a>)…<\/P> 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<\/STRONG> need permission to directly execute the statement which is in the DSE string<\/EM>). 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<\/a> function. This function is INVALUABLE for protecting isolated input values (which is another trick: isolate parameters if you can<\/EM>). <\/P> 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!<\/EM> :) <\/P> OK – so see the ProcedureParameters.sql (4.3 KB)<\/a> procedure to play with this within the CreditSampleDB.zip (55.79 KB)<\/FONT><\/STRONG><\/A>. You will need to modify some data to get rows to return to these queries; use the updates in the .sql script.<\/P> CREATE PROC dbo.GetMemberInfoParam SELECT m.* 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.<\/P> CREATE PROC dbo.GetMemberInfoParamDSE IF @LastName IS NULL AND @FirstName IS NULL AND @Member_no IS NULL DECLARE @ExecStr varchar(1000) SELECT @ExecStr = ‘SELECT m.* FROM dbo.member AS m WHERE ‘ <\/P> IF @LastName IS NOT NULL SELECT @ExecStr = @ExecStr + ISNULL(@LastName, ‘ ‘) EXEC(@ExecStr) 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! <\/P> I’m going to put together one more entry to bring all of these points together as well. Stay tuned!<\/P> Have fun!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[50,58,78],"tags":[],"class_list":["post-721","post","type-post","status-publish","format-standard","hentry","category-optimizing-procedural-code","category-resources","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=721"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/721\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n
\n
\n
\n
\n
(
@Lastname varchar(30) = NULL,
@Firstname varchar(30) = NULL,
@member_no int = NULL
)
AS
SET NOCOUNT ON<\/P>
\n
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<\/P><\/BLOCKQUOTE>
\n
\n
\n
(
@Lastname varchar(30) = NULL,
@Firstname varchar(30) = NULL,
@member_no int = NULL
)
AS
SET NOCOUNT ON<\/P>
\n
RAISERROR (‘You must supply at least one parameter.’, 16, -1)<\/P>
\n
, @MemberNoStr varchar(100)<\/P>
\n
\n
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)<\/P>
\n
+
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, ‘ ‘)<\/P>
\n
go<\/P><\/BLOCKQUOTE>
\n
\n
\n