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:

• Create a function which returns the value (could be constant or read a field from a table).
• Create a stored procedure and return the value through the OUTPUT parameter.

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

• Should the value be configurable by the user? If no, you may return the value by a function call. If the value is user configurable, then, you need a "setting table" to store the value.

• Will you store the value in a generic table structure (see below) or will you design a settings table to keep track of these settings?

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,

• We keep the bill number prefix, postfix and length in this table. It has three records,

  prop_group	prop_name	prop_value
  SYSTEM	bill-prefix	BILL/
  SYSTEM	bill-num-length	6
  SYSTEM	bill-postfix	/2021

  To retrieve these settings, you may use a SQL function.

    create function fn_get_bill_setting()
    returns @tb table  (
        bill_prefix nvarchar(20)
        , bill_len int
        , bill_postfix nvarchar(20)
    )
    as
    begin    
        insert into @tb (bill_prefix, bill_len, bill_postfix)
        select
            bill_prefix= (
                    select prop_value
                    from tb_sys_prop
                    where prop_name = 'bill-prefix'
                )
            , bill_len= (
                    select cast(prop_value as int)
                    from tb_sys_prop
                    where prop_name = 'bill-num-length'
                )
            , bill_postfix= (
                    select prop_value
                    from tb_sys_prop
                    where prop_name = 'bill-postfix'
                )
        return
    end
    go
  

  To get the bill settings,

    select *
    from dbo.fn_get_bill_setting()
  

  Another way is to retrieve the setting with a stored procedure

    create proc pr_get_bill_setting (
        @bill_prefix nvarchar(20) output
        , @bill_len int output
        , @bill_postfix nvarchar(20) output
    )
    as
    begin    
        select
            @bill_prefix = (
                    select prop_value
                    from tb_sys_prop
                    where prop_name = 'bill-prefix'
                )
            , @bill_len = (
                    select cast(prop_value as int)
                    from tb_sys_prop
                    where prop_name = 'bill-num-length'
                )
            , @bill_postfix= (
                    select prop_value
                    from tb_sys_prop
                    where prop_name = 'bill-postfix'
                )
    end
    go
  

  And here is how to use it,

    declare
        @bill_prefix nvarchar(20)
        , @bill_len int
        , @bill_postfix nvarchar(20)
  
    exec pr_get_bill_setting
        @bill_prefix output
        , @bill_len output
        , @bill_postfix output
  
    select @bill_prefix, @bill_len, @bill_postfix
  

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, software developer.