15 March 2010

Excel for SQL Developers – Part 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.

Excel - Get Stored Procedure 
     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.

No comments: