Dynamic sql statements | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic sql statements

I’m currently analysing a web app (ASP 3.0) with a SQL Server 2000 db. The web app is creating dynamic sql statements depending on user parameters. These sql statements are sent to sql server via ADODB.Command, therefore the sql statemnt can have a diiferent WHERE clause on every occasion depending on the user parameters.
What I’m trying to get opinions on, is whether it would be more efficient to have the user parameters passed to the db and from there the dynamic sql statements are created within the stored procedures.
I understand that it is better to not have dynamic sql statements , particuarly as this is a very busy site.
Advertise your IT vacancies for free at –http://www.ITjobfeed.com
Stored procedures can offer better protection against "injection" attacks, and will often improve execution times. Inside the stored procedures, you may find that using dynamic SQL can improve execution times, especially if you have lots of optional criteria. Whichever approach you use, make sure that you qualify all tables and views in your queries with the owner prefix, and understand the inherent performance pitfalls of cursors and UDFs. For dynamic SQL, look up sp_ExecuteSQL in Books Online — you can use this to get some of the performance benefits of stored procedures for ad-hoc queries.
In terms of execution plans , regardless of using stored procedures or ADODB. with sql statements, would a new execution plan need to be created for every different "version" of the sql statement? for example, if you had a stored procedure with a SELECT , e.g SELECT col1 FROM myTable WHERE type=2,
this would presumably create an excution plan , if then the next version of the sql statement , such as SELECT col1 FROM myTable WHERE type=2 AND colour=’red’ ,then this would create a new execution plan , even though it’s the same stored procedure
Advertise your IT vacancies for free at –http://www.ITjobfeed.com
The idea is that you limit the number of variations on the literal T-SQL query, by using parameters instead of literal criteria. Execution plans are cached for literal query statements, so the next time this executes SQL Server will have a better chance of finding a cached execution plan, eventhough the literal criteria are different.