util to convert between T-SQL & dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

util to convert between T-SQL & dynamic SQL

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-12 : 15:18:44

I am getting lazy and a little greedy here.

Dose any of you know an utility that can convert T-SQL to/from dynamic sql back and forth?

Just like a web site I know of, that converts C# to vb and vice versa, such a utility function will be really handy. There might be some thing in this nature out there.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 19:44:44

From static to dynamic is pretty simple just enclosed your existing query with single quote and assign to a varchar variable and hten exec() it


declare @sql varchar(max)
select @sql = '<your query>'
exec (@sql)

Don't think it is possible to convert from dynamic to static. As dynamic sql being dynamic, the query is form during run time and not defined in coding time.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-12 : 19:50:28

quote:
Originally posted by khtan

From static to dynamic is pretty simple just enclosed your existing query with single quote and assign to a varchar variable and hten exec() it

declare @sql varchar(max)
select @sql = '<your query>'
exec (@sql)

KH

Good one

Trying to be smart alec ha

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 19:51:33

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-13 : 09:12:36

You need dynamic sql only if you want to pass object names as parameters

Make sure you read this fully

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-13 : 09:17:35

quote:
Originally posted by Hommer

I am getting lazy and a little greedy here.

Dose any of you know an utility that can convert T-SQL to/from dynamic sql back and forth?

Just like a web site I know of, that converts C# to vb and vice versa, such a utility function will be really handy. There might be some thing in this nature out there.

Thanks!

Can you give an example? I am not sure what you are asking. Why would you want to do this? Dynamic SQL *is* T-SQL -- there is no difference in the code; the only difference is how it is executed.

- Jeff
http://weblogs.sqlteam.com/JeffS

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-06-13 : 16:26:00

Putting aside the argument of which one is better given a certain usage, I am frequently asked to convert back and forth.

Instance one:
SET @SQL = ' UPDATE A ' +
' SET A.p' + CAST (@txtMonth AS VARCHAR(8)) + ' = B.Promo ' +
' FROM #SalesHistory A JOIN #REPORT B ' +
' ON A.intYear = B.intYearStart ' +
' WHERE A.Year = ' + CAST (@intYear AS varchar(8))
--EXEC ( @SQL )
print @sql

two:
Embedded T-Sql
SQL = SQL + "Select A.col1, A.Col2"
SQL = SQL + " B.col1 ..."
cn.exec (SQL)

Three:
Access query has double quote around every fields, when promote it into a stored proc, removing all "s is the first step.
I use find/replace to replace " with empty string in QA...

Four:
Under Profiler trace, I got TextData. They are certainly look like t-sql, but you wouldn't be abale to run them in QA as is.

I know there might no be a total solution for all these cases, but I hope there are some custom function that at least can automate part of these tasks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-13 : 21:48:28

one: you need to use dynamic sql since it is a really, really bad database design.

two: remove the SQL from the application, create stored procedures. Make sure that even if the SQL is still being created and passed at the client that parameters are used.

three: again, use parameters and/or parameterized queries.

four: ????

The long and short of it is: you cannot just magically make your databases and application well-designed by using some automated custom function! The best thing you can do with a minimum of maintenance is to ensure that parameters are ALWAYS used, no exceptions, instead of concatenating and delimiting variables into sql strings. You can still use parameters, even with dynamically created sql statements.

Other than that, you need to redesign your databases and/or applications using best practices such as normalization, stored procedures, parameters, etc .... At the very least, make sure that new designs and development follows best practices from the very start.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources