IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TotalLikeSQLText') = 1
DROP PROC TotalLikeSQLText
GO
-- TotalLikeSQLText PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@SQLTEXT';
-- EXAMPLE:
--
-- EXEC TotalLikeSQLText '2009-03-10 00:00','2009-03-10 23:59','SEBOX1\SE12005','select * from'
CREATE PROC TotalLikeSQLText
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @SQLTEXT VARCHAR(100))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL ='SELECT ' + '''' + @SQLTEXT + '''' + ' SQL_SEARCH, ' +
'SUM(TIMESECS) TOTALSECS, ' +
'SUM(EXECS) TOTALEXECS, ' +
'AVGSECS = CASE WHEN SUM(EXECS) = 0 THEN 0 ' +
'ELSE ROUND(SUM(TIMESECS)/SUM(EXECS),4) END ' +
'FROM ( ' +
'SELECT DISTINCT H ' +
'FROM CONST_' + @DBID +
' WHERE UPPER(ST) LIKE UPPER(' + '''' + '%' + @SQLTEXT + '%' + '''' + ')) ST ' +
'INNER JOIN CON_STATS_SUM_' + @DBID + ' SS ON SS.SQLHASH = ST.H ' +
'LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) '
EXEC (@SQL)
END
GO
