Listing 1: Example SQLCommandNonQuery Subroutine That Uses Dynamic SQL

Private Sub SQLCommandNonQuery _ 
  (ByVal sServer As String, ByVal sDB As String) 
    Dim cn As New SqlConnection("SERVER=" & sServer _ 
        & ";INTEGRATED SECURITY=True;DATABASE=" & sDB) 
    Dim sSQL As String 
    Dim cmd As New SqlCommand(sSQL, cn) 
BEGIN Callout A 
    Try 
        ' Open the connection. 
        cn.Open() 
        ' First, drop the table if it already exists.
        sSQL = "IF EXISTS " _ 
           & "(SELECT * FROM dbo.sysobjects WHERE id = " _ 
           & "object_id(N'[Department]') " _ 
           & "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " _ 
           & "DROP TABLE [department]" 
        cmd.CommandText = sSQL 
        cmd.ExecuteNonQuery() 
END Callout A  
BEGIN Callout B  
       ' Then, create the table. 
        sSQL = "Create Table Department " _ 
           & "(DepartmentID Int Not Null, " _ 
           & "DepartmentName Char(25), Primary Key(DepartmentID))" 
        cmd.CommandText = sSQL 
        cmd.ExecuteNonQuery() 
END Callout B
 
    Catch e As Exception 
        MsgBox(e.Message) 
    End Try 
    ' Close the connection. 
    cn.Close() 
End Sub