One Plan Cache Implementation Technique with Adding Comment to Dynamic Query


Stan Futoma: https://triplessql.wordpress.com/

Add comment section to append to dynamic query to generate different plan cache.

The sample he posted in above positng:

  
 
ALTER PROCEDURE dbo.proc_GetMaxSubTotal @from DATETIME, @to DATETIME
AS
SET NOCOUNT ON;
DECLARE @comment NVARCHAR(MAX)='/* My query variant: '
+ CASE
WHEN DATEDIFF(day,@from,@to)<=1 THEN '1 day'
WHEN DATEDIFF(day,@from,@to)<=7 THEN '1 week'
ELSE 'long time'
END
+ ' */';

DECLARE @sql NVARCHAR(MAX)='
SELECT MAX(SubTotal)
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @from_dyn AND @to_dyn;
'+@comment

EXEC sp_executesql @sql, N'@from_dyn DATETIME,@to_dyn DATETIME', @from_dyn=@from, @to_dyn=@to

---execute the procedure with different parameters:

EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2006-01-02';
EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2009-12-31';
EXEC dbo.proc_GetMaxSubTotal '2006-01-02','2006-01-09';
 

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s