singleton_pattern

Singleton

The Singleton pattern is a design pattern that restricts the Instantiation of a class to one object. This is useful when exactly one object is needed to coordinate actions across the system. At first glance this appears to be relatively straightforward to implement using SQL as data tables are by their very nature of a single instance. The singleton pattern however is referring to the instantiation of a class that includes both methods and state.

I will approach the implementation of a singleton in SQL from the perspective that it is the state rather than the method that must be of a single value. I will note that I also get queasy feelings anytime I'm using a global method, but the technique does have its merit despite being overused, so I include it here.

---

---

In this example, the procedure <method> will instantiate and load the global table ##<method>_state with defaults if it does not exist. <method> then attempts to select <state> from ##<method>_state using @parameter=<value>. If the return value is null, a new entry is inserted into ##<method>_state as @parameter=<value> and @state=<state>. Finally, a recursive call is made to return the value of <state>.</state></state></value></method></value></method></state></method></method></method>

    create procedure <method> @parameter <type>, @state <type>, @output output
    as
        if object_id(N'tempdb..##<method>_state', N'P') is null
        begin
            create table ##<method>_state (<value> <type>, <state> <type>);
            insert into ##<method>_state (<value>, <state>) values
                (<value>, <state>)...;
        end

        set @output = (select <state> from ##<method>_state where <value> = @parameter);
        if (@output is null)
            insert into ##<method>_state (<value>, <state>) values
                (@parameter, @state)

        execute <method> @parameter=@parameter, @state=null, @output=@output output;
    go

---

---

copyright Katherine Elizabeth Lightsey 1959-2013 (aka; my life)

"The trouble is not that I am single and likely to stay single, but that I am lonely and likely to stay lonely." - Charlotte Brontë

---

---