storedprocedures.bas

CodeWizardRC2.zip for Rainbow Souce Code

Attribute VB_Name = "StoredProcedures"

'Added Table creation function for RC2 - RV 2003/12/09
Function GetCreateTable(oTable As SQLDMO.Table) As String
    Dim rb_Prefix As String
    Dim s As String
    Dim i As Integer
    Dim col As SQLDMO.Column
    Dim Key As String
    Dim ParameterKey As String
    Key = GetIdentityKey(oTable)
    ParameterKey = "@" & Key
    If InStr(oTable.name, "rb_") > 0 Then
        rb_Prefix = "rb_"
    End If
    

    
    s = s & "if not exists (select * from dbo.sysobjects where id = object_id(N'[" & oTable.name & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & vbCrLf
    s = s & "BEGIN" & vbCrLf
    s = s & vbTab & "CREATE TABLE [" & oTable.name & "] (" & vbCrLf
    'Get fields
    i = 1
    For Each col In oTable.Columns
        'Get column name and data type
        s = s & vbTab & vbTab & "[" & col.name & "] [" & col.DataType & "] "
        'Check for Identity
        If col.Identity Then
            s = s & "IDENTITY(" & col.IdentitySeed & ", " & col.IdentityIncrement & ") "
        End If
        'Check NULLS Status
        If col.AllowNulls Then
            s = s & "NULL"
        Else
            s = s & "NOT NULL"
        End If
        'Add a comma
        s = s & "," & vbCrLf
    Next
    s = s & vbTab & vbTab & "CONSTRAINT [PK_" & oTable.name & "] PRIMARY KEY NONCLUSTERED" & vbCrLf
    s = s & vbTab & vbTab & "(" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[" & Key & "]" & vbCrLf
    s = s & vbTab & vbTab & ")," & vbCrLf
    s = s & vbTab & vbTab & "CONSTRAINT [PK_" & oTable.name & "_Modules] FOREIGN KEY" & vbCrLf
    s = s & vbTab & vbTab & "(" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[ModuleID]" & vbCrLf
    s = s & vbTab & vbTab & ") REFERENCES [" & rb_Prefix & "Modules] (" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[ModuleID]" & vbCrLf
    s = s & vbTab & vbTab & ") ON DELETE CASCADE NOT FOR REPLICATION" & vbCrLf & vbCrLf
    s = s & ")" & vbCrLf
    s = s & "END" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    'Create the Staging table
    s = s & "if not exists (select * from dbo.sysobjects where id = object_id(N'[" & oTable.name & "_st]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & vbCrLf
    s = s & "BEGIN" & vbCrLf
    s = s & vbTab & "CREATE TABLE [" & oTable.name & "_st] (" & vbCrLf
    'Get fields
    i = 1
    For Each col In oTable.Columns
        'Get column name and data type
        s = s & vbTab & vbTab & "[" & col.name & "] [" & col.DataType & "] "
        'Check for Identity
        If col.Identity Then
            s = s & "IDENTITY(" & col.IdentitySeed & ", " & col.IdentityIncrement & ") "
        End If
        'Check NULLS Status
        If col.AllowNulls Then
            s = s & "NULL"
        Else
            s = s & "NOT NULL"
        End If
        'Add a comma
        s = s & "," & vbCrLf
    Next
    s = s & vbTab & vbTab & "CONSTRAINT [PK_" & oTable.name & "_st] PRIMARY KEY NONCLUSTERED" & vbCrLf
    s = s & vbTab & vbTab & "(" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[" & Key & "]" & vbCrLf
    s = s & vbTab & vbTab & ")," & vbCrLf
    s = s & vbTab & vbTab & "CONSTRAINT [PK_" & oTable.name & "_st_Modules] FOREIGN KEY" & vbCrLf
    s = s & vbTab & vbTab & "(" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[ModuleID]" & vbCrLf
    s = s & vbTab & vbTab & ") REFERENCES [" & rb_Prefix & "Modules] (" & vbCrLf
    s = s & vbTab & vbTab & vbTab & "[ModuleID]" & vbCrLf
    s = s & vbTab & vbTab & ") ON DELETE CASCADE NOT FOR REPLICATION" & vbCrLf & vbCrLf
    s = s & ")" & vbCrLf
    s = s & "END" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    GetCreateTable = s

End Function

Function GetCreateTriggers(oTable As SQLDMO.Table) As String
    Dim s As String
    Dim i As Integer
    Dim col As SQLDMO.Column
    Dim Key As String
    Dim ParameterKey As String
    Key = GetIdentityKey(oTable)
    ParameterKey = "@" & Key
    
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & oTable.name & "_stModified]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)" & vbCrLf
    s = s & "DROP TRIGGER [" & oTable.name & "_stModified]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf
    
    s = s & "CREATE TRIGGER [" & oTable.name & "_stModified]" & vbCrLf
    s = s & "ON [" & oTable.name & "_st]" & vbCrLf
    s = s & "FOR DELETE, INSERT, UPDATE" & vbCrLf
    s = s & "AS" & vbCrLf
    s = s & "BEGIN" & vbCrLf
    s = s & vbTab & "DECLARE ChangedModules CURSOR FOR" & vbCrLf
    s = s & vbTab & vbTab & "SELECT ModuleID" & vbCrLf
    s = s & vbTab & vbTab & "From inserted" & vbCrLf
    s = s & vbTab & vbTab & "Union" & vbCrLf
    s = s & vbTab & vbTab & "SELECT ModuleID" & vbCrLf
    s = s & vbTab & vbTab & "From deleted" & vbCrLf & vbCrLf
    s = s & vbTab & "DECLARE @ModID  int" & vbCrLf & vbCrLf
    s = s & vbTab & "OPEN ChangedModules" & vbCrLf & vbCrLf
    s = s & vbTab & "FETCH NEXT FROM ChangedModules" & vbCrLf
    s = s & vbTab & "INTO @ModID" & vbCrLf & vbCrLf
    s = s & vbTab & "WHILE @@FETCH_STATUS = 0" & vbCrLf
    s = s & vbTab & "BEGIN" & vbCrLf
    s = s & vbTab & vbTab & "EXEC ModuleEdited @ModID" & vbCrLf & vbCrLf
    s = s & vbTab & vbTab & "FETCH NEXT FROM ChangedModules" & vbCrLf
    s = s & vbTab & vbTab & "INTO @ModID" & vbCrLf
    s = s & vbTab & "END" & vbCrLf
    s = s & vbTab & "Close ChangedModules" & vbCrLf
    s = s & vbTab & "DEALLOCATE ChangedModules" & vbCrLf
    s = s & "END" & vbCrLf
    s = s & "GO" & vbCrLf
    GetCreateTriggers = s
End Function

Function GetDeleteSP(oTable As SQLDMO.Table) As String
    Dim s As String
    Dim TableName As String
    Dim ProcName As String
    Dim Key As String
    Dim ParameterKey As String
    
    Key = GetIdentityKey(oTable)
    ParameterKey = "@" & Key
    
    'Get the Desired table name and procedure name
    Call GetTableProcNames(oTable.name, "Delete", TableName, ProcName)
    'Comment
    s = s & GetDrop(ProcName & TableName)
    s = s & "/* Procedure " & ProcName & TableName & "*/" & vbCrLf
    'Head
    s = s & "CREATE PROCEDURE " & ProcName & TableName & vbCrLf
    'Parameters
    s = s & ParameterKey & " " & oTable(Key).DataType & vbCrLf
    'AS
    s = s & "AS" & vbCrLf
    'Code
    s = s & "DELETE" & vbCrLf
    s = s & "FROM" & vbCrLf & vbTab & oTable.name & vbCrLf
    s = s & "WHERE" & vbCrLf & vbTab & Key & " = " & ParameterKey & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf
    
    GetDeleteSP = s
End Function

Function GetSelectSingleSP(oTable As SQLDMO.Table) As String
    Dim s As String
    Dim i As Integer
    Dim TableName As String
    Dim ProcName As String
    Dim col As SQLDMO.Column
    Dim Key As String
    Dim ParameterKey As String
    
    Key = GetIdentityKey(oTable)
    ParameterKey = "@" & Key
    
    'Get the Desired table name and procedure name
    Call GetTableProcNames(oTable.name, "GetSingle", TableName, ProcName)
    'Comment
    s = s & GetDrop(ProcName & TableName)
    s = s & "/* Procedure " & ProcName & TableName & "*/" & vbCrLf
    'Head
    s = s & "CREATE PROCEDURE " & ProcName & TableName & vbCrLf
    'Parameters
    s = s & ParameterKey & " " & oTable(Key).DataType & vbCrLf
    'AS
    s = s & "AS" & vbCrLf
    'Code
    s = s & "SELECT" & vbCrLf
    i = 1
    For Each col In oTable.Columns
        s = s & vbTab & col.name
        If i < oTable.Columns.Count Then
            s = s & ","
            i = i + 1
        End If
            s = s & vbCrLf
    Next
    s = s & "FROM" & vbCrLf & vbTab & oTable.name & vbCrLf
    s = s & "WHERE" & vbCrLf & vbTab & Key & " = " & ParameterKey & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf
    
    GetSelectSingleSP = s
End Function

Function GetSelectSP(oTable As SQLDMO.Table) As String
    Dim s As String
    Dim i As Integer
    Dim TableName As String
    Dim ProcName As String
    Dim NameLen As Integer
    Dim col As SQLDMO.Column
    Dim Key As String
    Dim ParameterKey As String
    Dim ModIDExists As Boolean
        
    ModIDExists = ColumnExists(oTable, "ModuleID")