User:KELightsey/sandbox/SQL Command Design Pattern

SQL Command Design Pattern

The word command is defined as "to direct with authority; give orders to" and "to have at one's disposal". It implies having both the ability and resources to make something happen.

The software the command design pattern is a behavioral design pattern in which an object is used to represent and encapsulate all the information needed to call a method at a later time. This information includes the method name, the object that owns the method and values for the method parameters.

The following example implements xml objects as commands using the SQL language. These command objects are then passed to an <execution> engine for processing.

Note that the resultant design is not intended to model an ideal scenario as design requirements are unique to each application. The design is solely intended to illustrate the use of SQL components as command pattern objects.

Example

For the example an <execution> engine will be built. XML will be used to build up a typed XML object that contains both the SQL statement and parameters required for sp_executesql. The execution engine well execute the <command> object using the parameters applied, and return the output as part of the object.

<command> object

An xml schema collection is used to type a <command> object as:

<command>
    <receiver>
		<parameters />
		<sql />
	</receiver>
    <sender />
</command>

[design_pattern].[xsd_command]

create xml schema collection [design_pattern].[xsd_command] as 
N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="command">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="receiver" minOccurs="1" maxOccurs="1">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="parameters" type="xs:string" minOccurs="1" />
              <xs:element name="sql" type="xs:string" minOccurs="1" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
          <xs:element name="sender" minOccurs="1" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
		<xs:any minOccurs="0" maxOccurs="unbounded" processContents="lax"/>
              </xs:sequence>
            </xs:complexType>
          </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>';

<execution> engine

The <execution> engine for the <command> object. Note that the <execution> engine has no knowledge of the actual command being run nor of the output being returned. All details of the command itself including output are encapsulated in the <command> object.

[design_pattern].[run]

create procedure [design_pattern].[run]
    @command xml([design_pattern].[xsd_command]),
    @output  xml output
as
    declare @sql [nvarchar](max) = 
            @command.value(N'(/command/receiver/sql)[1]', N'[nvarchar](max)'),
        @parameters [nvarchar](max) = 
            @command.value(N'(/command/receiver/parameters)[1]', N'[nvarchar](max)');

    execute sp_executesql
        @sql       =@sql,
        @parameters=@parameters,
        @output    =@output output;

Client Code

[design_pattern].[get]

Sample method to show use of [design_pattern].[run].

create procedure [design_pattern].[get]
    @command [xml] output,
    @output  xml ([design_pattern].[xsd_command])
as
    declare @builder [xml];
    execute [design_pattern].[run]
        @command=@command,
        @output =@builder output;

    set @builder = N'<output>'
                   + cast(@builder as [nvarchar](max))
                   + N'</output>';
    set @command.modify(N'insert sql:variable("@builder") as last into (/command/sender)[1]');

Multiple examples are shown to illustrate the execution engine handling different types of <command> objects.

Example 1

declare @command xml([design_pattern].[xsd_command])= N'<command>
        <receiver>
            <parameters>@output [xml] output</parameters>
            <sql>set @output = (select [name] as N''@name'', 
				[object_id] as N''@object_id'', 
				[type_desc] as N''@type_desc'' from [sys].[objects]
				order by [type_desc], [name]
                for xml path(''output''), root(N''output_tree''));</sql>
        </receiver>
		<sender />
    </command>';
declare @output [xml];

execute [design_pattern].[get]
    @command=@command output,
    @output =@output;

select @command as N'[design_pattern].[get]';

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-- output
<command>
  <receiver>
    <parameters>@output [xml] output</parameters>
    <sql>set @output = (select [name] as N'@name', 
				[object_id] as N'@object_id', 
				[type_desc] as N'@type_desc' from [sys].[objects]
				order by [type_desc], [name]
                for xml path('output'), root(N'output_tree'));</sql>
  </receiver>
  <sender>
    <output>
      <output_tree>
        <output name="check_customer_name" object_id="1899205866" type_desc="CHECK_CONSTRAINT" />
		  ...
        <output name="system_objects_hierarchy" object_id="1657772963" type_desc="VIEW" />
      </output_tree>
    </output>
  </sender>
</command>
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

Example 2

declare @command xml([design_pattern].[xsd_command])= N'<command>
        <receiver>
            <parameters>@output [xml] output</parameters>
            <sql>set @output = (select [name], [object_id] from [sys].[tables]
				order by [object_id]
                for xml path(''table''), root(N''table_tree''));</sql>
        </receiver>
		<sender />
    </command>';
declare @output [xml];

execute [design_pattern].[get]
    @command=@command output,
    @output =@output;

select @command as N'[design_pattern].[get]';
 
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-- output
<command>
  <receiver>
    <parameters>@output [xml] output</parameters>
    <sql>set @output = (select [name], [object_id] from [sys].[tables]
				order by [object_id]
                for xml path('table'), root(N'table_tree'));</sql>
  </receiver>
  <sender>
    <output>
      <table_tree>
        <table>
          <name>test_01</name>
          <object_id>32107255</object_id>
        </table>
		  ...
        <table>
          <name>test_cross_db_ri</name>
          <object_id>2048062382</object_id>
        </table>
      </table_tree>
    </output>
  </sender>
</command>
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------