I'm running some SQL scripts to find the value of named URL parameters because I need to look through the SSRS ReportServer's history of ran reports and get a grip on which parameters users are selected. I wrote a T-SQL function to parse the value of the parameters field as follows:-
USE [ReportServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnParseParameter]
(
-- Add the parameters for the function here
@paramText varchar(1000), @paramName varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @paramValue varchar(50)
declare @paramPrefix varchar(55)
declare @lenPrefix int, @startPos int
-- parameter name incl. equals char.
select @paramPrefix = @paramName + '='
select @lenPrefix = len(@paramPrefix)
-- move cursor to end of parameter prefix
select @startPos = charindex(@paramPrefix, @paramText) + @lenPrefix
select @paramValue = substring(@paramText, @startPos, len(@paramText))
-- cut off at the first '&' unless we're at end of parameter string
if(charindex('&', @paramValue, 0) > 0)
begin
select @paramValue = substring(@paramValue, 0, charindex('&', @paramValue, 0))
end
RETURN @paramValue
END