KnowDotNet

Convert Stored Proc to Dynamic SQL

Using the Clipboard in a Macro

by Les Smith

How do I use the Clipboard in a Macro?  It does not appear to work!  Can a Macro be written to convert a Stored Procedure to Dynamic SQL?  

First, you are probably asking why would I want to convert a Stored Procedure to Dynamic SQL?  The answer is simple; I want to test a stored procedure in an environment where the stored procedure does not yet exist.  It is a read-only stored procedure so I wont cause any problem in the new environment.  However, the code is faiirly complex and it would be nice to have a Macro build the code for the creation of the dynamic SQL rather than having to do it myself.  Besides that, it allows me to demonstrate how to use the clipboard.from a Macro in .NET.

You would think that the Clipboard object would work the same in a Macro as it does in an application.  That would not be correct.  If you simply try to use the clipboard in a Macro, your code will fail due to the way that macros work at the behest of the IDE.  The error you will get is the old "object not set to an instance of an object" message.  Therefore, to make a long story short, you need to create a separate thread to access the Clipboard.

The following macro code will retrieve the selected portion of a Stored Procedure, that I have alread copied to the Clipboard.  Next, it will generate VB.NET code to create a dynamic SQL statement.  Finally, it will place the completed code back on the Clipboard.  Actually, the most complex thing it does is create the Threads to get from the Clipboard and put the results back on the Clipboard.  Generating the code for creation of the dynamic SQL is a piece of cake.

In addition to generating the code for creating the SQL statement, I use a simple Regular Expression to change the way the parameters are referenced in the dynamic SQL as oppossed to the way they are referenced in a Stored Procedure.  The comments in the code describe each step of the process.

Imports System.Text.RegularExpressions
Imports System.Web
Imports System.Collections
Imports System.Threading
Imports System.Windows.Forms
Imports System.IO
Imports System.Text

Public Module Module1
    Dim clipString As String = String.Empty
    Public Sub ConvertStProcToDynSQL()
        Dim cbThread As Threading.Thread = _
           New Threading.Thread(AddressOf GetClipboardText)
        With cbThread
            .ApartmentState = ApartmentState.STA
            .IsBackground = True
            .Start()
            ' Wait for clipboard action  
            .Join()
        End With
        cbThread = Nothing

        ' if there is anything from the clipboard surround with
        ' double quotes
        If clipString.Length > 0 Then
            Dim sr As New StringReader(clipString)
            Dim sb As New StringBuilder(1000)
            Dim line As String = String.Empty
            sb.Append("Dim sql As String = String.Empty" & vbCrLf)
            Do
                line = sr.ReadLine
                sb.Append("sql &= " & Chr(34) & line & " " & Chr(34) & vbCrLf)
            Loop Until line Is Nothing

            ' replace the stored procedure parameters
            clipString = _
              Regex.Replace(sb.ToString, "@(?<parm>\w+)", """ & ${parm} & """)

            ' put the results back on the clipboard
            ClipBoardThread = New Threading.Thread(AddressOf PutTextOnClipboard)
            With ClipBoardThread
                .ApartmentState = ApartmentState.STA
                .IsBackground = True
                .Start()
                ' Wait for clipboard action
                .Join()
            End With
            ClipBoardThread = Nothing
        End If
    End Sub
    Sub GetClipboardText()
        clipString = _
          Clipboard.GetDataObject() _
            .GetData(System.Windows.Forms.DataFormats.StringFormat)
    End Sub

    Sub PutTextOnClipboard()
        Clipboard.SetDataObject(clipString, True)
    End Sub

To use the macro, go to SQL Server Query Query Analyzer (2000) or Management Studio ((2005) and select the T-SQL code that you want to convert and copy it to the Clipboard.  After you run the macro, put your cursor in the code window where you wish to place the converted code and press CTRL V to paste the code into the window.  There you will see the code to build the dynamic SQL.  For example, the function below was generated by the macro.  The only code that I typed manually is the Function definition line, the call to SqlHelper, and the End Function lines.  All of the other code was generated by the macro.

   Private Function dsGetResults(ByVal caseID As Integer, ByVal groupName As String) As DataSet
      Dim sql As String = String.Empty
      sql &= "SELECT first_name,last_name,p.ID, "
      sql &= "   Case  "
      sql &= "     When DateCompleted is null Then '' "
      sql &= "        When isnull(custGrade,'') = 'P' Then 'P' "
      sql &= "        When isnull(custGrade,'') = 'F' Then 'F' "
      sql &= "        When isnull(custGrade,'') = 'R' Then 'R' "
      sql &= "        Else Case "
      sql &= "           When isnull(calcedGrade,'')='P' Then 'P' "
      sql &= "           When isnull(calcedGrade,'')='F' Then 'F' "
      sql &= "           When isnull(calcedGrade,'')='R' Then 'R' "
      sql &= "        Else 'R' "
      sql &= "        End "
      sql &= "    End Grade, "
      sql &= " "
      sql &= "  DateCompleted,t.calcedGrade, t.custGrade,  "
      sql &= "  p.CustID, p.Datereceived,  "
      sql &= "FROM mydb..Personnel P  (nolock) "
      sql &= "LEFT Outer Join oldgis..TSummary T (nolock) on p.id = t.CaseID "
      sql &= "WHERE  p.ID = " & caseID & " "
      sql &= "  And p.custid in "
      sql &= "    (select custid from everycustomer..customegroup E (nolock) "
      sql &= "     where " & groupName & " = E.GroupName) "
      sql &= "Order by  Datereceived desc "
      Return SqlHelper.ExecuteDataset(My.Settings.OldGis, CommandType.Text, sql)
   End Function

What the Regex.Replace does is converts the following two lines of generated code to the second set of two lines, which changes the way the parameters are accessed in the two different environments.  The first two lines are from the original stored procedure access method.

      sql &= "WHERE  p.ID = @caseID "
      sql &= "     where @groupName = E.GroupName) "

They have to be changed to the following to work in building the dynamic SQL.

      sql &= "WHERE  p.ID = " & caseID & " "
      sql &= "     where " & groupName & " = E.GroupName) "


I hope this will be of help to you, either in showing you how to use the Clipboard in a Macro, or in Macro writing itself.

Have you tried our newest product, Visual Class Organizer?  You'll be amazed how easy it is to keep the code in your code windows organized.  TRY IT FREE FOR 30 DAYS BY CLICKING HERE.



Ask a Question, or give your feedback on my articles or products by going to the KnowDotNet Forum or by clicking on My Blog.