UDF's and Parameters

UDF's and Parameters

Passing user entered parameters to UDF's requires special consideration depending on the way in which the XML report is constructed. The SubstituteFor <Condition> type can not be used to provide parameters to temporary tables as it is only applied to the final select statement.

Parameter applied to main select query .

SubstituteFor can be used as in the following example,

Select dbo.FormatGridRef(Sample.Spatial_ref, Sample.Spatial_Ref_system, {0} )
<Condition field ="" operator="equal" type="OptionSet" SubstituteFor="{0}" name="Square Size" >

</Condition>

When the user makes the choice the value is substituted in the final query thus passing the parameter to the User Defined Function (UDF). The above example is for a numeric fields. For text fields the substitute character in the select statement needs to be in single quotes (eg. '{0}' ) . The JNCC circle reports use SubstituteFor in this way.

Parameter needed to populate temporary table.

If a parameter is needed to populate a temporary table then SubstituteFor can not be used. One of the following approaches can be used.

1. If the value required for the parameter can be obtained from a table within the database.

Create a temporary table to hold the parameter.
Select the parameter into the temporary table.
Use the entry from the temporary table in the UDF.

Example

CREATE TABLE #MYFILTER (MEASUREMENT_TYPE_KEY CHAR(16))
INSERT INTO #MYFILTER (Measurement_Type_Key)
SELECT Measurement_Type_key from Measurement_type
WHERE ('<Condition field="Measurement_Type.Short_Name ="Measurement Type" operator="equal" type="Text"/>')

If the result can generate more than one entry then the a tie breaker must be incuded in the call to the UDF so that only one value is passed. Try to avoid this situation as it may produce random results for the user.

2. If the value is numeric

Create a temporary table to hold the parameter
Populate this with a query which will put in the value as a string suffixed with " = "
Use this table in the UDF having removed the suffix and converted it to an integer

Example:

CREATE TABLE #MYFILTER (MYFILTER VARCHAR (20))
INSERT INTo #MYFILTER (MYFILTER)
VALUES ('<Condition field="" name="A Number" operator="equal" type="Number"/>')

In the above example the user will be required to enter a number (eg 123456). This will be stored in the table #MYFILTER. as " = 123456"

The query to use this then needs to convert this back into a number. CAST(RIGHT(#MYFILTER.MYFILTER,LEN(#MYFILTER.MYFILTER)-3) AS INT). The #MYFILTER table have to be declared in the FROM clause, but as there is only one entry it does not need to be joined to the other tables used.

You can also use OptionSet to return the values. Both numeric and text can be supplied in this way as follows.

Update #MYFilter set NAMEOFFIELD <Condition field="" operator="equal" type='OptionSet'
name = "Whatever" >

</Condition>

In the above case 1 or 2 will be inserted into the temporary table depending on the option chosen by the user. If the column to be populated is a character column then the values should be in single quotes within the double quotes. (eg. value="'ABC'")

3. If the value is a string

If the choice of values is limited then you can use OptionSet as in 2 above as above .

CREATE TABLE #MYFILTER (TEXTCOLUMN as varchar(20))

INSERT INTO #MYFILTER ('A') (This just creates record for you to update)

Update #MYFilter set NAMEOFFIELD <Condition field="" operator="equal" type='OptionSet'
name = "Whatever" >

<Option name = "My name " value = "'A'" /> (there is a double and single quote around the A and B)
<Option name = "MY name2" value = "'B'" />

</Condition>

For totally free format text use

CREATE TABLE #MYFILTER (TEXTCOLUMN as varchar(20))

INSERT INTO #MYFILTER ('A') (This just creates record for you to update)

Update #MYFilter set NAMEOFFIELD <Condition field="" operator="equal" type="Text"
name = "Whatever" />

OR using a variable

Decalre @MyFilter
set @MyFilter <Condition field="" operator="equal" type="Text"
name = "Whatever" />

4. If the Value is a date

You can supply a vague date via a <condition> tag but this needs ome manipulation. Create two columns in your temporary table. one a varchar with a length of 100 charcaters (say VDATE varcjhar(100) and one as an integer (say VDATENUMERIC. Populate the varchar field using the following

UPDATE #MyFilter set VDATE = '
<Condition field="" operator="equal" type="vagueDate" name="Date From" entrycount ="1" />'

then update the integer value to get the start date as an integer

UPDATE #Myfilter set VDATENUMERIC = cast(substring(VDATE,22,(Charindex('AND',VDATE)-23)) AS int)

This could be adapted slightly to get the end date if this is required.