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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
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.
