Dynamic SQL/EXEC Question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic SQL/EXEC Question

One more question for you guys: Regarding the use of Dynamic SQL and EXEC statements, is there any performance benefit to using one option over the other, as described below? The first option checks whether to include a particular case in the WHERE clause, if so it adds to the SQL string the exact syntax that would have been used were this not dynamic SQL. The second option performs the same check whether to include a particular case in the WHERE clause, if so it adds a case to the WHERE clause, injecting the actual value of the variable into the string. SELECT @orderid = ‘1000’
SELECT @sql =
‘SELECT :confused:rderID, :confused:rderDate, od.UnitPrice
FROM Orders o JOIN [Order Details] od ON :confused:rderID = od.OrderID
WHERE 1 = 1′ /*
FIRST OPTION, USE ONE BIG STRING
RESULTS IN " AND :confused:rderID = @orderid "
*/
IF @orderid IS NOT NULL
SELECT @sql = @sql + ‘ AND :confused:rderID = @orderid’ /*
SECOND OPTION, INJECT THE SQL STRING WITH THE PARAM’S ACTUAL VALUE
RESULTS IN " AND :confused:rderID = 1000 "
*/
IF @orderid IS NOT NULL
SELECT @sql = @sql + ‘ AND :confused:rderID = ‘ + @orderid EXEC(@sql)

There’s no choice…you have to use the 2nd one…. Try them both out….
Brett :cool:
Dynamic SQL! You should use EXEC() only in the case when your string to be executed is larger than 4,000 characters. In any other case you should go for sp_executesql. SQL Server 2000 improved on caching dynamic statements, and the use of sp_executesql will give you better chances that a cached query plan will be reused, thus giving you better performance. In addition to this you might also wnat to read http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Frank
http://www.insidesql.de
http://www.familienzirkus.de
]]>