Parse Browser Query String parameter for stored procedure

Question

I need to pass information from one screen to another and have a RunProcedure call on the second screen use that information.

I plan to add a query special parameter from the OpenURL call but then need to capture that parameter and pass it to a procedure on the second screen.

Answer 1

As of dbFront 1.4.0.0186 you can select the value {url(query)} to pass the Query String to a Stored Procedure Parameter.

You can then use SQL to parse the Query String and extract the needed value.

TSQL Example: fnGetURLParameter()

CREATE FUNCTION [dbo].[fnGetURLParameter](
	@paramName [varchar](16),
	@queryString [varchar](2048)
) RETURNS [varchar](1024) AS 
BEGIN
	-- Strip initial part of URL
	declare @paramsStart int = charindex('?', @queryString);
	if (@paramsStart > 0)
		set @queryString = substring(@queryString, @paramsStart + 1, len(@queryString));

	-- Find Parameter=@paramName and return value into @paramValue.
	declare @paramValue varchar(1024) = null;
	select @paramValue = substring(value, len(@paramName) + 2, 256)
	From STRING_SPLIT (@queryString, '&', 1) 
	WHERE SUBSTRING(value,1,len(@paramName) + 1) = concat(@paramName, '=');

	-- Return the Parameter
	return @paramValue;
END;

Example Usage

declare @pQueryString varchar(256) = '?k=value1&department=value2&t=value3'
select dbo.fnGetURLParameter('department', @pQueryString);
-- Returns: 'value2'
GO