I am embarking on on a large project and have an inquiry about stored procedures in general.
Let's say I have a
UserKey IDENTITY NOT NULL PRIMARY KEY FirstName nvarchar(50)NOT NULL LastName nvarchar(50) NOT NULL Email nvarchar(50) NOT NULL Birthdate date NULL EffectiveFrom datetimeoffset NOT NULL EffectiveThru datetimeoffset NULL
In reality, there are some more fields here, but for the sake of brevity I have skimmed it down to the basics.
Now, I am faced with creating stored procedures to allow basic CRUD functionality for this table.
The Insert sproc is simple enough.
CREATE PROCEDURE [dbo].[usp_INSERT_User] ( --@UserKey BIGINT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @Email NVARCHAR(50), @BirthDate DATE = NULL, @EffectiveFromDtTm DATETIMEOFFSET(7), @EffectiveThruDtTm DATETIMEOFFSET(7) = NULL, )
My main inquiry is how I should write an update sproc.
Since there can be pretty granular updates, should I create an update stored procedure for each of those granular updates?
For example, let's say I only want to update the user's email after processing a verified email update for the user, would I write a sproc with one parameter?
If the user updated their profile, I could write a sproc like:
usp_UPDATE_User @UserKey, @FirstName, @LastName, @Email, @Birthdate
So, to cover my most common update operations, I might end up with a few sprocs to cover use cases of updates to the
User table. Obviously, the larger the table and/or the higher number of business requirements would have a direct correlation to the number of update sprocs written for a particular table. This gets me thinking about maintainability of a ton of sprocs.
Yesterday, I went down the rabbit hole of
"oh, why don't I just write ONE end-all-be-all update sproc that can take whatever parameter I want to update via an XML param"
Luckily, without using dynamic sql, I was able to create that monstrosity. And it works.
I then decided to test speeds between my fancy xml sproc and just a regular update sproc (with multiple params) with very similar updates.
So, I created a mock user table with 12 million records and tested both approaches. Unsurprisingly, the XML approach on average, took 7x longer than the vanilla update sproc.
There is also additional overhead on the other side of the wire serializing parameters into XML, which could be of concern with a large update batch.
Now, I say all of that to wrap up and ask which type of approach in your experience is most often (if at all) seen? Is is most common practice to have granular sprocs like
I like the simplicity of having a golden sproc that can take however many number of parameters you want to pass it and update them accordingly, but there are also performance drawbacks.
To give more context: running SQL Server 2019. I am also pretty new at this stuff.
Thanks for any help!