Wednesday, June 1, 2011

Tool that generates Dynamic T-SQL!

A few days ago, my blog reached 100.000 visits. Given this opportunity I decided to try to do something special, for example write a pretty cool article or even better, build a SQL-Related tool and give it as a freeware via my blog!

Before talking about the tool, first of all I would like to say a huge THANK YOU for your support. The 100.000 visits did not take place by machines (oh well, some visits might came from bots :)) but real persons. Persons that are passionate about technology, persons just like me.

I have always believed in technical communities and the exchange of knowledge within them. To share technical knowledge is a different way of communication; a communication based on the same passion for technology. You want to know something; you just ask about it. You want to share something; you just answer a question or write about it. You want to discuss something; you leave a comment. The communication within a technical community is quite simple. I think that I have just described its main principles :) I believe that this is the reason that there are so many technical communities worldwide as well as so many distinguished community leaders.

So, I would just like thank you again for your support and jump immediately to the description of the tool!

First, here’s two screenshots of the tool:







As you can see the tool’s name is “Dynamic T-SQL Generator”.

The rationale behind this tool is quite simple. There is many times where you might need to embed a T-SQL statement for dynamic execution. For example you may have the following query:

SELECT * FROM tCustomer WHERE NAME=’JOHN’

Then let's say you want to include the above query to a Database Mail send statement:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email_address@goes.here',
@importance='Normal',
@body='Email body goes here.',
@subject ='Email subject goes here',
@profile_name ='Profile_name',
@query ='SELECT * FROM tCustomer WHERE NAME=''JOHN''',

@attach_query_result_as_file = 1,
@query_attachment_filename ='Query_Results.txt'

As you can see in the above example, the query needed to be enclosed in single quotation marks as well as the name value "JOHN" in the WHERE clause needed two additional single quotation marks. Now, if your query is a very simple one, it is not a hassle to manually add the single quotation marks. However, most of the times this is not the case :) You usually have a really large query that you may need to embed it as a parameter to a stored procedure, etc.

To this end, wouldn’t it be cool to have a tool that transforms a static T-SQL statement into a dynamic one and vice versa? Guess what; this is what exactly “Dynamic T-SQL Generator” does!

Analytically the functionality of the tool is the following:

  • Given a static T-SQL statement it generates the corresponding dynamic T-SQL statement.
  • Given a dynamic T-SQL statement it generates the corresponding static T-SQL statement.
  • Ability to restore the original T-SQL statement.
  • Ability to convert the T-SQL statement to uppercase.
  • Ability to convert the T-SQL statement to lowercase.
  • Ability to copy the T-SQL statement to the clipboard.
  • Ability to paste the contents of the clipboard into the text box.
Prerequisites
The only prerequisite for executing the tool is having installed Microsoft .NET Framework 3.5 SP1.

Downloading the Tool
If you like to get the tool, you can download it from here!

I hope you find it useful!

By the way, drop me a line if you liked the tool!

Cheers!


[Update 2016-12-06]: Artemiou Data Tools is now SQLArtBits! Visit the new website to check out all the cool SQL Server tools!
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: