// T-SQL Tuesday 161
This months T-SQL Tuesday is number 161 and is hosted by Reitse Eskens (T, L, B) - you can read the original invitation here or by clicking the image below.
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.
The script
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.
☕