Stored Procedures

Much like web developers will create an API endpoint that performs a specific function. DBAs have a similar tool in the toolbox, a stored procedure. Much like a single API endpoint, a stored procedure is a reusable piece of script that can be exposed to the outside world. More specifically, those that can establish a connection to the server.

Stored procedures can also be executed from another script. First, a quick and easy stored procedure:

-- Indicate the database to persist the stored procedure
use SampleDatabase

-- Drop the stored procedure if it already exists
if object_id( 'dbo.QueryTable', 'P' ) is not null  
    drop procedure dbo.QueryTable  
go

-- Create or re-create the procedure
-- Accept an argument of tableName
create procedure dbo.QueryTable 
  @tableName varchar(50)
as
  declare @sqlStatement varchar(max)

  set @sqlStatement = concat('select * from ', @tableName)

  exec(@sqlStatement)
go

With the stored procedure created, it can be executed by another script.

use SampleDatabase

exec dbo.QueryTable 'SomeTable'

While this particular stored procedure is not the most useful, it will return the results of select * from SomeTable. Now, imagine there was something more interesting to create a store procedure. Say creating a dynamic table schema, based on an existing table to house analysis data.

use SampleDatabase

if object_id( 'dbo.CreateDataTypeAnalysisTable', 'P' ) is not null  
    drop procedure dbo.CreateDataTypeAnalysisTable  
go

create procedure dbo.CreateDataTypeAnalysisTable 
  @tableName varchar(50)
as
  declare @sqlStatement varchar(max)

  -- Concatenate the  column information to string, copying the original table
  -- row by row then appending an additional varchar(50) column of {columnName}Type
  select @sqlStatement = coalesce(@sqlStatement + ', ', '') + 
    concat(column_name
      , ' '
      , case 
        when DATA_TYPE = 'varchar' 
        then 
          case 
            when CHARACTER_MAXIMUM_LENGTH = -1 
            then 'varchar(max)' 
            else concat('varchar(', CHARACTER_MAXIMUM_LENGTH, ')' ) 
            end 
        else DATA_TYPE 
        end
      , ',  '
      , column_name
      , 'Type varchar(50)')
  from INFORMATION_SCHEMA.COLUMNS
  where table_name = @tableName 

  -- Prepend the database and the create table query, then concat the string of columns from above
  set @sqlStatement = concat('use SampleDatabase ; create table dbo.', @tableName, 'TypeAnalysis (  ', @sqlStatement, ')')

  -- Execute the query, creating the analysis table
  exec (@sqlStatement)
go

Given that we have a whole set of tables, we can use the INFORMATION_SCHEMA meta-data tables again to create the analysis tables for all tables in the database.

use SampleDatabase

declare @sqlStatement varchar(max)

-- Append the stored procedure execution for each table together
select @sqlStatement = coalesce(@sqlStatement + ';', '') + 
  concat('exec dbo.CreateDataTypeAnalysisTable '''
    , table_name
    , '''')
from INFORMATION_SCHEMA.TABLES

-- Prepend the database to execute against
set @sqlStatement = concat('use SampleDatabase ; ', @sqlStatement)

-- Execute the collection of stored procedures creating all the analysis tables
execute(@sqlStatement)

With 1 stored procedure and a quick query, we can create an endless amount of tables programmatically. I feel like this is only scratching the surface of what can be done. So much power in so few query lines. More to come in the next ETL series post.