Add or Read Version Number to the SQL Stored-Procedures or Functions

Question

I need to a version number in the SQL StoredProcedures and Functions to know what version is using now and necessary to update that or not ?

After searching the Internet I found the table sys.objects from the master database.
sys.objects can be fetch SQL elements information like this code:

SELECT *
FROM   sys.objects
WHERE  TYPE         = 'P'  -- StoredProcedures
       OR  TYPE     = 'FN' -- Functions
       OR  TYPE     = 'U'  -- Usert Tabels
           

Result:

╔══════╦═══════════╦═══════════╦══════╦═══════════╦═════════════╦═════════════╦═════╗
║ name ║ object_id ║ schema_id ║ type ║ type_desc ║ create_date ║ modify_date ║ ... ║
╚══════╩═══════════╩═══════════╩══════╩═══════════╩═════════════╩═════════════╩═════╝

But still I could not find a field with the name Version or MetaData for SQL stored procedures or functions to store some thing like version in that!

My Questions is:

How do know what version number of StoresProcedures or Functions is in use?
And if exist this property, how to change it ?

Or if it's not exist (Version field) then can I change sys.objects data programmatically ? because I try to change that and seen this error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

Answer 1

Well you can update the system catalog, but it's very dirty and I won't suggest it anyway.

Another idea could be just to add a comment over each procedure/function.

A more dynamical way can be a to add a DDL Trigger which will count the versions automatically.

Or if you just restrict every procedure/function in a database to have a version, you may have a DDL Trigger, which will parse the code for the version number. Afterwards it will log this in a version table. If the version isn't present, you rollback the change and avoid a change. The same can be handled if the version for the specific procedure already exists.

But anyway, these solutions need to be build and may have some (small) side-effects. Depending on their nature of design. But in your case you probably want to have multiple versions of a procedure and behave differently on the call of each one. In this case the comment variant may be the easiest and efficient one for your use-case.

You can also use the extended Properties which need to be set. Maybe this will be a solution you want to live. But this won't avoid duplicate version numbers as described in the DDL Trigger.

Another way, if you just want to version your code, can be the usage of GIT + SSDT.