Stefan Cullmann wrote:
Richard, sorry, this change would return the very same amount of data as before.
That's exactly right - the changes are totally benign to F&L but provide a crib for local tailoring as shown below which restricts the data returned (for a filter of the form somefield='somevalue'):
ALTER PROCEDURE [dbo].[UserDefinedTable_GetRows] @TabModuleId int AS BEGIN
DECLARE @ModuleID INT = ( SELECT TOP 1 ModuleID FROM dbo.TabModules WHERE TabModuleId = @TabModuleId )
DECLARE @Filter NVARCHAR (2000) = ( SELECT TOP 1 SettingValue FROM dbo.TabModuleSettings WHERE TabModuleId = @TabModuleId AND SettingName = 'UDT_Filter' )
DECLARE @ComparitorPos INT = CHARINDEX ( '=' , @Filter )
DECLARE @FilterValue NVARCHAR (2000) = REPLACE ( SUBSTRING ( @Filter, @ComparitorPos + 1, 2000 ), '''', '' )
DECLARE @FilterFieldTitle NVARCHAR (50) = LEFT ( @Filter, @ComparitorPos - 1 )
DECLARE @FilterFieldID INT = ( SELECT TOP 1 UserDefinedFieldID FROM dbo.UserDefinedFields WHERE FieldTitle = @FilterFieldTitle )
SELECT R.UserDefinedRowId, F.FieldTitle, D.FieldValue FROM dbo.UserDefinedRows R
LEFT OUTER JOIN dbo.UserDefinedData D on R.UserDefinedRowId = D.UserDefinedRowId
INNER JOIN dbo.UserDefinedFields F on D.UserDefinedFieldId = F.UserDefinedFieldId
WHERE R.ModuleId = @ModuleId
AND R.UserDefinedRowID IN ( SELECT UserDefinedRowID FROM dbo.UserDefinedData WHERE
UserDefinedFieldID = @FilterFieldID AND CAST (FieldValue AS NVARCHAR(2000) ) = @FilterValue )
ORDER by R.UserDefinedRowID
END
I think it only needs a very slight tweak in the VB Code to swap ModuleId fro TabModuleId ...
Cheers, Richard