The topic of this month is about our most fun T-SQL script. It can be either a procedure or statement that I’ve written.
My fun T-SQL script
A couple of years back I needed to find the latest used parameters from a reporting services - SSRS (yes, it is old!) report. The functionality was to find the latest used parameters for all parameters dynamically and use them as the default values for each parameter in the report, when tjhe user opened the report. If the uesr had not used the reprot before, it should ask for the parameters.
This was a alot of fun for me to do, as I needed to query the internal tables from the reporting services database. The tables ExecutionLogStorage and the Catalog was on play. This due to the fact that I needed the active user’s latest used parameter values from each specific report and this in runtime when the report was opened.
Yes, at that point in time I had a somewhat bad habbit of using caps lock when writing T-SQL statements.
CREATE Procedure [dbo].[GetReportParameters] ( @ReportName as varchar(50), @UserName as varchar(50), @Parameter as nvarchar(max) = '' ) as SELECT TOP 1 @Parameter = CAST(els.Parameters AS varchar(MAX)) FROM ReportServer.dbo.ExecutionLogStorage AS els INNER JOIN ReportServer.dbo.[Catalog] AS c ON c.ItemID = els.ReportID WHERE 1=1 and c.Name = @ReportName and els.UserName = @UserName ORDER BY els.TimeStart DESC DECLARE @VarStringHeader as varchar(max) DECLARE @VarStringValue as varchar(max) DECLARE @NextString NVARCHAR(max) DECLARE @Pos INT DECLARE @NextPos INT DECLARE @String NVARCHAR(max) DECLARE @Delimiter NVARCHAR(max) SET @String = @Parameter SET @Delimiter = '&' SET @String = @String + @Delimiter SET @Pos = charindex(@Delimiter,@String) CREATE TABLE #Result (Header Varchar(max), Value varchar(max)) WHILE (@pos <> 0) BEGIN SET @NextString = substring(@String,1,@Pos - 1) select @VarStringHeader = SUBSTRING(@NextString,1,Charindex('=',@NextString,1)-1) select @VarStringValue = replace(SUBSTRING(@NextString,Charindex('=',@NextString,1)+1,LEN(@NextString)),'%26','&') insert into #Result select @VarStringHeader as Header, @VarStringValue as Value SET @String = substring(@String,@pos+1,len(@String)) SET @pos = charindex(@Delimiter,@String) END DECLARE @columns VARCHAR(max) SELECT @columns = COALESCE ( @columns + ',[' + Header + ']', '[' + Header+ ']' ) FROM #Result GROUP BY Header ORDER BY Header DECLARE @query VARCHAR(8000) SET @query = 'SELECT * FROM ( SELECT Header, Value FROM #Result ) PIV PIVOT (max(Value) FOR Header in (' + @columns + ')) AS chld' EXECUTE (@query) DROP TABLE #Result GO
A final word
I’ve always been fond of playing around with T-SQL statements and figure out fun stuff with them. Another fun part of T-SQL is the thing that I’ve seen around with getting the engine to draw stuff - like this one from Brent Ozar - link.
Thank you to Reitse for this months fun part of the T-SQL world. I hope you also liked it.