SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 best way to convert to SPROC?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

105 Posts

Posted - 01/22/2013 :  10:57:38  Show Profile  Reply with Quote
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

105 Posts

Posted - 01/24/2013 :  16:11:47  Show Profile  Reply with Quote
no one? :(
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 01/24/2013 :  16:28:31  Show Profile  Reply with Quote
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

105 Posts

Posted - 01/27/2013 :  18:32:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 01/28/2013 :  13:21:11  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 01/28/2013 13:22:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000