August 12, 2013 at 2:16 pm
I walked into a project where they are re-writing old SQL 2000 code from the ground up but making little changes to the database structure (which is a mess). The original programmers had most of the SQL embedded in VB code. In order to populate their grids they would build a SQL statement in VB variables and pass it to the database to execute, returning a dataset. I want to re-write all this as stored procedures. They stored column information in a table and looped through with VB and built a dynamic query. I would like to do the same in T-SQL but want to avoid cursors. What is the best way to handle this? With a CTE?
August 12, 2013 at 2:49 pm
A more practical example would be useful to understand what you're trying to do.
Can you share some sample data, table definations and desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 2:58 pm
First, welcome to SSC!
If I understand your question correctly you have some VB code that accepts parameters, builds a query based on those parameters then sends that query (ad hoc) to SQL Server which returns the results. If that is the case then, yes, re-doing this using stored procedures is the way to go.
stevethesql (8/12/2013)...but want to avoid cursors. What is the best way to handle this? With a CTE?
Read this:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
-- Itzik Ben-Gan 2001
August 12, 2013 at 6:42 pm
Sounds like you're looking for a way to build a catch all query in the SP.
Take a look at this article by Gail Shaw: SQL-in-the-Wild: Catch-all queries[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply