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 2008 Forums
 Transact-SQL (2008)
 best way to convert to SPROC?

Author  Topic 

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-22 : 10:57:38
so im upgrading an old bit of code into new code.
one of which involves converting the SQL statements being generated dynamically into SPROCs.

now unfortunately, some of the pages contain multiple textboxes, dropdownlists, date controls etc....

of course, it will be a pain to convert to SPROCs because you need to check to see what values have been populated in order to execute that query.

and there are multiple tables to be joined too, depending on certain conditions based on the input values.

question is: is there an easier way to convert the dynamically generated SQL into SPROCs rather than having to check every single param (at least 12) to see if it is NULL just to add another clause to the WHERE statement?

Many thanks!

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-24 : 16:11:47
no one? :(
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-24 : 16:28:31
I don't know of any simple method or tool that will allow you to convert adhoc SQL to stored procedures given what you are describing - i.e., the SQL depending on the values of the user interface controls.

Perhaps the only way is the hard and the correct way, which is to understand the queries that are going into the database server and refactor them to pass the values from user interface controls as parameters to one or more stored procedures that you will write based on your knowledge of the queries currently being sent.
Go to Top of Page

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-27 : 18:32:37
yes. I just thought there would be a clever or smarter way of doing so or even if I need to do what I was intending (what you suggested), if there is some trick to remember...to make it easier
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-28 : 13:21:11
Without more information it'd be hard to offer much advice. I try to avoid Dynamic SQL. But it is a tool and, used properly, it can perform better than a standard query with lots of optional predicates. I can't tell from your description if you are doing a "more" dynamic query (meaning different tables or joins) versus a "catch-all" query that just had optional/nullable predicates.

Here is a link about Dynamic SQL (that may or may not help):
http://www.sommarskog.se/dynamic_sql.html

And here is a link about catch-all queries:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

If you wanted to provide more detail we might be able to help guide you a little better.
Go to Top of Page
   

- Advertisement -