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.

Below is a TSQL example function.

CREATE FUNCTION [dbo].[fnGetURLParameter](
	@paramName [varchar](16),
	@queryString [varchar](2048)
) RETURNS [varchar](255) AS 
BEGIN
	declare @paramValue varchar(12) = null;

	-- Strip initial '?'
	if (substring(@queryString, 1, 1) = '?')
		set @queryString = substring(@queryString, 2, 256);

	-- Find Parameter=@paramName and return value into @paramValue.
	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;
GO

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