September 15, 2015 at 10:17 am
I wondering if anyone knows of any best practices related to something I'm seeing in several stored procedures at my new job. I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
FROM dbo.Customers
WHERE RecordID = @customerID
END
ELSE IF @rptType = 2
BEGIN
SELECT Address1, Address2, City, StateAbbr, Zip
FROM dbo.CustomerAddresses
WHERE @customerID = @customerID
END
ELSE IF @rptType = 3
BEGIN
SELECT C.LastName, C.FirstName, C.MiddleInitial,
CA.Address1, CA.Address2, CA.City, CA.StateAbbr, CA.Zip
FROM dbo.Customers AS C
INNER JOIN dbo.CustomerAddresses AS CA ON CA.CustomerID = C.RecordID
WHERE C.RecordID = @customerID
END
END
GO
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold. First, does anyone know of any best practices related to this type of thing. Second, if there are best practices for one way or the other, are they documented somewhere that I can point to as supporting documentation?
Thanks in advance for any help.
September 15, 2015 at 10:33 am
https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2015 at 5:01 pm
tDub44 (9/15/2015)
the output depends on the given report type
In the example you provide the WHERE clause is a simple "PKey = @customerID" and is suspect whatever parameter is provided on the first instantiation of the SProc is going to give a reasonable query plan for all possible sub-choices (except, perhaps, a @customerID that does not exist or NULL).
But probably you have more complex examples ...
My view is that its fine until you have a performance problem; at that point change the poor performing cases to an EXEC of a Child Procedure which just wraps that one SQL statement.
We do this type of code a lot, but we also have loads of @Parameters MOST of which will be NULL - i.e. "don't care". We create the actual query as dynamic SQL and execute it as a parametrized query with sp_ExecuteSQL and thus, hopefully!, get optimal query plans for each variation of WHERE clause (and SELECT list)
September 16, 2015 at 11:28 am
Maybe you could do something like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
SET NOCOUNT ON
IF @rptType = 1
EXEC dbo.rpt_1 @customerID
ELSE IF @rptType = 2
EXEC dbo.rpt_2 @customerID
ELSE IF @rptType = 3
EXEC ...
GO --end of proc
Edit: If you're trying to return a result set, not sure if that will work the same or not. But you can keep one "driver" proc but still only gen the actual plans required by the reports being run instead of having to parse all of the sql for the first (and maybe later) reports.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2015 at 12:05 pm
ScottPletcher (9/16/2015)
If you're trying to return a result set, not sure if that will work the same or not.
We do both ways and I can't say I've ever known a child-proc behave any different to an all-in-one. We have "improved" all-in-one Procs to have Child-Procs, I don't remember ever having to change anything else.
But it wouldn't be the fist time I've had to eat my hat!!
We have
SET NOCOUNT ON
SET XACT_ABORT ON
so we don't have to worry about struggling to catch an error from the Child Proc (or it causing a rollback that means the Parent is running outside the transaction it started), but they could be causes for concern otherwise I suppose?
For anyone grappling with that particular issue it is worth a read of Erland Sommarskog article on Error Handling. Its a long read though ...
September 16, 2015 at 12:13 pm
Kristen-173977 (9/16/2015)
ScottPletcher (9/16/2015)
If you're trying to return a result set, not sure if that will work the same or not.We do both ways and I can't say I've ever known a child-proc behave any different to an all-in-one. We have "improved" all-in-one Procs to have Child-Procs, I don't remember ever having to change anything else.
But it wouldn't be the fist time I've had to eat my hat!!
We have
SET NOCOUNT ON
SET XACT_ABORT ON
so we don't have to worry about struggling to catch an error from the Child Proc (or it causing a rollback that means the Parent is running outside the transaction it started), but they could be causes for concern otherwise I suppose?
For anyone grappling with that particular issue it is worth a read of Erland Sommarskog article on Error Handling. Its a long read though ...
I generally believed the sub-proc would return a result to caller of the parent proc -- otherwise, I wouldn't have suggested it -- but I wasn't 100% sure of it, esp. under all conditions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply