Hard Coding SQL Queries - Pros & Cons

Hello experts,

We developed a web application that reads some statistical information from a SQL database. The software company that developed this web application has used hard-coded SQL queries in about 5% of the charts.

The problem is our information is so dynamic and hence using hard coded queries is not recommended.

I'm trying to assess the pros and cons of using hard coded SQL queries, so I would appreciate your help in providing me with links or documents on whether or not we should use Hard Coding in SQL queries.

Thank you,
MehawitchiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Locate your hard coded SQL scripts and post them one at a time describing the intent of each script and the type and number of dynamic variable you want to apply to each. This may be a good start for you.

I don't think there will be some particular sites on "How to make hard coded SQL dynamic." The requirement is too general and includes all there is on the work done in databases.

That said, you may be able to get some info from:
http://www.dba-sql-server.com/sql_server_tips/t_super_sql_460_dynamic_sql.htm
http://stackoverflow.com/questions/15440260/dynamic-sql-taking-much-much-longer-than-the-hard-coded-equivalent
http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Mike
MehawitchiAuthor Commented:
Thank you eghtebas for your reply.

I'm not asking to replace the hard-coded SQL queries with dynamic ones. I'm only asking for articles, white papers or links about the pros and cons of using hard codes in web applications in general and SQL queries in particular.
Mike EghtebasDatabase and Application DeveloperCommented:
Until someone could post a link you are looking for, I would like to share an experience I had with a project I did for a company in Los Angeles. This application had 8 reports to produce. After I made the SQL scripts dynamic getting criteria from 7 synchronized list boxes, we were able to produce: 35 x 8 = 280 reports (a 280 fold improvement).

If a report is static with a criteria like Country = 'USA' then we are stuck with one choice. However, with dynamic SQL, we can have a list of 196 countries in a list box to choose from to set it as the dynamic criteria , then we increase our choice of reports possibly by 196 folds.

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.