MS Office: Excel for SQL Developers III (Creating a Stored Procedure from Table’s Metadata)

In a previous post I was showing a simple technique for using stored procedures for inserting/updating data within SQL Server 2005+. From a rough estimation I think that about 80% of the stored procedures built for this purpose could use the same template, without including additional business logic. Given the fact that databases store metadata about their objects, table’s metadata could be used to create such a stored procedure using VBA or more complex programming languages. SQL Server 2008 stores its table’s columns metadata in INFORMATION_SCHEMA.COLUMNS view, the required information for the Products created in the previous post could be get using the following query:

-- SQL Server 2008 Table/Column Metadata SELECT TABLE_SCHEMA SchemaName 
, TABLE_NAME TableName 
, COLUMN_NAME ColumnName 
, DATA_TYPE DataType 
, CHARACTER_MAXIMUM_LENGTH Length 
, IS_NULLABLE IsNullable 
, NUMERIC_PRECISION Precision 
, NUMERIC_SCALE NumericScale 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Products' 
ORDER BY TABLE_NAME 
, ORDINAL_POSITION 

The output of the above query could be easily copy-pasted into Excel or directly exported using the SQL Server Import and Export Wizard, the range with data (without headers) being passed as parameter to GetStoredProcedure macro created for this post together with stored procedure and table’s name.

 

Macro’s construction is based on the following presumptions:
1.   The first attribute in the target table is always table’s primary key.
2.   The stored procedure considers only simple data types though it can be extended to support further data types.
3.   Stored procedure's name and table's name contains also the schemas in which the respective objects are created.
4.   CreationDate, ModifiedDate, CreatedBy and ModifiedBy are reserved words for tracking the timestamp and the User who created/modified the record.
5.   The UserID  will be the last parameter in case the CreatedBy or ModifiedBy are in scope.

And here’s the macro GetStoredProcedure with its afferent Metadata Enumeration that maps the column order from the above query, and the GetDataType function that translates the metadata related to data type to the actual parameters’ data type: 

 

Enum Metadata 
  enmSchemaName = 1 
  enmTableName = 2 
  enmColumnName = 3 
  enmDataType = 4 
  enmLength = 5 
  enmIsNullable = 6 
  enmPrecision = 7 
  enmNumericScale = 8 
End Enum 

Function GetStoredProcedure(ByVal rng As Range, ByVal ProcedureName As String, ByVal TableName As String) As String 
'builds the code for a stored procedure based on table's metadata 
Dim parameters As String 
Dim columnName As String 
Dim parameterName As String 
Dim columnValue As String 
Dim insert As String 
Dim columns As String 
Dim update As String 
Dim indexRow As Integer 
Dim ID As String 
Dim insertUserID As Boolean 

insertUserID = False 

For index = 1 To rng.Rows.Count 
    If Len(Trim(Cells(index, Metadata.enmTableName).Value)) > 0 And Len(Trim(Cells(index, Metadata.enmColumnName))) Then 
       'translating column names 
       Select Case rng.Cells(index, Metadata.enmColumnName) 
       Case "CreationDate": 
            columnName = "" 
            columnValue = "GetDate()" 
            parameterName = "" 
       Case "ModifiedDate": 
            columnName = "ModifiedDate" 
            columnValue = "GetDate()" 
            parameterName = "" 
       Case "CreatedBy": 
            columnName = "" 
            columnValue = "@UserID" 
            parameterName = "" 
            insertUserID = True 
       Case "ModifiedBy": 
            columnName = Trim(rng.Cells(index, Metadata.enmColumnName)) 
            columnValue = "@UserID" 
            parameterName = "" 
            insertUserID = True 
       Case Else: 
            columnName = Trim(rng.Cells(index, Metadata.enmColumnName)) 
            columnValue = "@" & columnName 
            parameterName = "@" & columnName 
       End Select 
       
       If index = 1 Then 
            ID = columnName 
       Else 
            columns = columns & ", " & Trim(rng.Cells(index, Metadata.enmColumnName)) 
            insert = insert & ", " & columnValue 
            
            If Len(columnName) > 0 Then 
                 update = update & Space(9) & ", " & columnName & " = " & columnValue & "" & vbCrLf 
            End If 
       End If 
       
        If Len(parameterName) > 0 Then 
             parameters = parameters & ", " & parameterName & " " & GetDataType(Trim(rng.Cells(index, Metadata.enmDataType).Value), Trim(rng.Cells(index, Metadata.enmLength).Value), Trim(rng.Cells(index, Metadata.enmPrecision).Value), Trim(rng.Cells(index, Metadata.enmNumericScale).Value)) & vbCrLf 
         End If 
    End If 
Next 

If Len(columns) > 0 Then 
    columns = Right(columns, Len(columns) - 1) 
End If 
If Len(insert) > 0 Then 
    insert = Right(insert, Len(insert) - 1) 
End If 
If Len(update) > 0 Then 
    update = Right(update, Len(update) - 10) 
End If 
If Len(parameters) > 0 Then 
    parameters = Right(parameters, Len(parameters) - 1) & IIf(insertUserID, ", @UserID int" & vbCrLf, "") 
End If 

'building the stored procedure string 
GetStoredProcedure = "CREATE PROCEDURE " & ProcedureName & "(" & vbCrLf & parameters & ")" & vbCrLf & _ 
    "AS" & vbCrLf & _ 
    "BEGIN" & vbCrLf & _ 
    "BEGIN TRY" & vbCrLf & _ 
    "    IF ISNULL(@" & ID & ", 0) = 0" & vbCrLf & _ 
    "    BEGIN" & vbCrLf & _ 
    "    -- insert statement" & vbCrLf & _ 
    "       INSERT INTO " & TableName & " (" & columns & ")" & vbCrLf & _ 
    "       VALUES (" & insert & ")" & vbCrLf & _ 
    "       SET @" & ID & " = @@IDENTITY" & vbCrLf & _ 
    "    END" & vbCrLf & _ 
    "    ELSE" & vbCrLf & _ 
    "    BEGIN" & vbCrLf & _ 
    "        -- update statement" & vbCrLf & _ 
    "        UPDATE " & TableName & vbCrLf & _ 
    "        SET " & update & _ 
    "        WHERE " & ID & "= @" & ID & vbCrLf & _ 
    "    END" & vbCrLf & _ 
    "    SELECT @" & ID & vbCrLf & _ 
    "END TRY" & vbCrLf & _ 
    "BEGIN CATCH" & vbCrLf & _ 
    "   SELECT Cast(ERROR_NUMBER() as varchar(10)) + ':' + ERROR_MESSAGE()" & vbCrLf & _   
    "END CATCH" & vbCrLf & _ 
    "END" 

End Function 

Private Function GetDataType(ByVal DataType As String, ByVal Length As String, ByVal NumericPrecision As String, ByVal NumericScale As String) As String 
'translating data types 
Select Case DataType 
    Case "varchar": GetDataType = DataType & "(" & Length & ")" 
    Case "nvarchar": GetDataType = DataType & "(" & Length & ")" 
    Case "nchar": GetDataType = DataType & "(" & Length & ")" 
    Case "decimal": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "numeric": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "money": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case "smallmoney": GetDataType = DataType & "(" & NumericPrecision & "," & NumericScale & ")" 
    Case Else: GetDataType = DataType 
End Select 

End Function 

Note:
As always, there is enough place for improvement, I just tried to exemplify the utility of such a function that could reduce considerably developers’ time for building such stored procedures.