Welcome to ciysys blog

Load the system settings using SQL stored procedure or SQL function

Published on: 20th Sep 2012

Updated on: 18th May 2021

Overview

Stop hard-coding the constants in a stored procedure. Instead, you should read the value from your "settings" table or return the constant value by a function call. In C# and other programming languages, it allows you to declare a constant and use it anywhere in the program. But, in MSSQL, you can't do that.

Our approach

To get the constant or settings, you may try to do this:

Something to consider before deciding how to design the stored procedure/function:

The generic table for keeping the system settings

In our database design, we have a generic table that stores any kind of setting.

create table tb_sys_prop (
    sys_prop_id bigint not null             --the primary key.
    , prop_group nvarchar(255) not null     --the setting group.
    , prop_name nvarchar(255) not null      --the setting name.
    , prop_value nvarchar(max) null         --the setting value

    , modified_on datetime null
    , modified_by nvarchar(255) null
    , created_on datetime null
    , created_by nvarchar(50) null
);

For example,

The trade off between SQL function and stored procedure

It seems that SQL function is easier to load the settings as compared to stored procedure. But, SQL function does not allow you to patch the records in tb_sys_prop by using INSERT or UPDATE.

create function fn_some_function()
returns int
as
begin
    --try to patch the setting
    update tb_sys_prop
    set prop_value='1'
    where
        prop_name = '2'
        and prop_value <> '1'

    return 3
end
go

When you try to use the UPDATE statement in SQL function, the following error will occur.

Msg 443, Level 16, State 15, Procedure fn_test12, Line 7
Invalid use of a side-effecting operator 'UPDATE' within a function.

So, if you plan to fix any missing system setting automatically upon retrieval, it's best to stay with the stored procedure.

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.