storedprocedures.bas

CodeWizardRC2.zip for Rainbow Souce Code

    Key = "ModuleID"
    ParameterKey = "@" & Key
    
    'Get the Desired table name and procedure name
    Call GetTableProcNames(oTable.name, "Get", TableName, ProcName)
    NameLen = Len(oTable.name)
    If InStr(NameLen - 1, oTable.name, "es") > 0 Then
        TableName = TableName & "es"
    ElseIf InStr(NameLen, oTable.name, "s") > 0 Then
        TableName = TableName & "s"
    End If
    'Comment
    s = s & GetDrop(ProcName & TableName)
    s = s & "/* Procedure " & ProcName & TableName & "*/" & vbCrLf
    'Head
    s = s & "CREATE PROCEDURE " & ProcName & TableName & vbCrLf
    
    If ModIDExists Then
        'Parameters
        s = s & ParameterKey & " " & "int" & vbCrLf
    End If
    
    '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
    'Fixed bug, added check for ModudelID RV-2003/12/12
    If ModIDExists Then
        s = s & "WHERE" & vbCrLf & vbTab & "ModuleID = @ModuleID" & vbCrLf
    End If
    s = s & "GO" & vbCrLf & vbCrLf

    GetSelectSP = s
End Function

Function GetAddSP(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, "Add", TableName, ProcName)
    'Comment
    s = s & GetDrop(ProcName & TableName)
    s = s & "/* Procedure " & ProcName & TableName & "*/" & vbCrLf
    'Head
    s = s & "CREATE PROCEDURE " & ProcName & TableName & vbCrLf
    'Parameters
    i = 1
    For Each col In oTable.Columns
    s = s & vbTab & "@" & col.name & " " & GetSQLFullDataType(col)
    If col.Identity Then s = s & " OUTPUT"
    If i < oTable.Columns.Count Then
        s = s & ","
        i = i + 1
    End If
        s = s & vbCrLf
    Next
    'AS
    s = s & "AS" & vbCrLf
    'Code
    s = s & "INSERT INTO " & oTable.name & vbCrLf
    s = s & "(" & vbCrLf
    i = 2
    For Each col In oTable.Columns
        If Not col.Identity Then
            s = s & vbTab & col.name
            If i < oTable.Columns.Count Then
                s = s & ","
                i = i + 1
            End If
                s = s & vbCrLf
        End If
    Next
    s = s & ")" & vbCrLf
    'VALUES
    s = s & "VALUES" & vbCrLf
    s = s & "(" & vbCrLf
    i = 2
    For Each col In oTable.Columns
        If Not col.Identity Then
            s = s & vbTab & "@" & col.name
            If i < oTable.Columns.Count Then
                s = s & ","
                i = i + 1
            End If
                s = s & vbCrLf
        End If
    Next
    s = s & ")" & vbCrLf
    'RETURN VALUE
    
    If (oTable.Columns(Key).IsRowGuidCol) Then
    Else
        s = s & "SELECT" & vbCrLf
        s = s & vbTab & ParameterKey & " = @@Identity" & vbCrLf
    End If
    
    s = s & "GO" & vbCrLf & vbCrLf

    GetAddSP = s
End Function

Function GetUpdateSP(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, "Update", TableName, ProcName)
    'Comment
    s = s & GetDrop(ProcName & TableName)
    s = s & "/* Procedure " & ProcName & TableName & "*/" & vbCrLf
    'Head
    s = s & "CREATE PROCEDURE " & ProcName & TableName & vbCrLf
    'Parameters
    i = 1
    For Each col In oTable.Columns
    s = s & vbTab & "@" & col.name & " " & GetSQLFullDataType(col)
    If i < oTable.Columns.Count Then
        s = s & ","
        i = i + 1
    End If
        s = s & vbCrLf
    Next
    'AS
    s = s & "AS" & vbCrLf
    'Code
    s = s & "UPDATE " & oTable.name & vbCrLf
    s = s & "SET" & vbCrLf
    i = 2
    For Each col In oTable.Columns
        If Not col.Identity And Not col.IsRowGuidCol Then
            s = s & vbTab & col.name & " = " & "@" & col.name
            If i < oTable.Columns.Count Then
                s = s & ","
                i = i + 1
            End If
                s = s & vbCrLf
        End If
    Next
    s = s & "WHERE" & vbCrLf & vbTab & Key & " = " & ParameterKey & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf
    
    GetUpdateSP = s
End Function


Function GetUninstallSP(oTable As SQLDMO.Table) As String
    Dim s As String
    Dim inputTableName As String
    Dim TableName As String
    Dim ProcName As String
    Dim NameLen As Integer
    
    inputTableName = oTable.name
        
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & inputTableName & "_stModified]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)" & vbCrLf
    s = s & "drop trigger [" & inputTableName & "_stModified]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    Call GetTableProcNames(inputTableName, "Get", TableName, ProcName)
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & ProcName & TableName & "s]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure [" & ProcName & TableName & "s]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    Call GetTableProcNames(inputTableName, "GetSingle", TableName, ProcName)
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & ProcName & TableName & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure [" & ProcName & TableName & "]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    Call GetTableProcNames(inputTableName, "Update", TableName, ProcName)
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & ProcName & TableName & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure [" & ProcName & TableName & "]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    Call GetTableProcNames(inputTableName, "Add", TableName, ProcName)
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & ProcName & TableName & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure [" & ProcName & TableName & "]" & vbCrLf
    s = s & "GO" & vbCrLf & vbCrLf

    Call GetTableProcNames(inputTableName, "Delete", TableName, ProcName)
    s = s & "if exists (select * from sysobjects where id = object_id(N'[" & ProcName & TableName & "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & vbCrLf
    s = s & "drop procedure [" & ProcName & TableName & "]" & vbCrLf
    s = s & "GO" & vbCrLf

    GetUninstallSP = s
End Function