December 16, 2010 at 4:31 am
Hello,
This is a 3th topic involving my current project: Dynamic Reports with SQL Server (An assignment I got last month, while we are waiting for new projects)
I've gotten so far that a user can select the database, table, columns, number of records, columns to filter upon and the values to be applied to the filter columns.
The report will only show the first 10 columns (this might be increased if a way can be found to let a report table grow or shrink based on the number of columns in a resultset which itself would be derived from a dynamic SQL)
At this point the stored procedure, called dynamicSQL, is in its 3th version, and we would like to make
A)Improvements to its current mechanics
B)Put a size limit on its results (Limiting the dataset in kb so it won't be a big factor on the network)
Topics for this can be found 'Is there a way to put a (default) limit on a dataset size.' and 'How best to calculate the size of a resultset (in kb) without the use of datalength'
The premise of the topic is to get help and insight into removing an ugly loop still present in the dynamicSQL stored procedure.
The attachement dynamicSQL3 holds the create for the current stored procedure, dynamicSQLFilter2 holds a function that basicly returns me the the filters and/or errormessages and the functions include the 2 functions needed to make some parts work.
Let now look at the part that I want to get rid off and improve
SET @SQLa = 'INSERT INTO #filters '
SET @SQLa = @SQLa + 'SELECT ds1.Item As FilterField,'
SET @SQLa = @SQLa + '(SELECT IFS.DATA_TYPE FROM ' + @Database + '.INFORMATION_SCHEMA.COLUMNS IFS WHERE IFS.COLUMN_NAME = ds1.Item'
SET @SQLa = @SQLa + ' AND IFS.TABLE_NAME = ''' + @t2 + ''' AND IFS.TABLE_SCHEMA = ''' + @s1 + ''') As FFDataType,ds2.Item As FilterValue'
SET @SQLa = @SQLa + ' FROM [dbo].[DelimitedSplit8K] (''' + @FilterFields + ''',''' + @ffd +''') ds1'
SET @SQLa = @SQLa + ' FULL OUTER JOIN [dbo].[DelimitedSplit8K] ('''+ @Filter_Values +''',''' + @fvd +''') ds2 on ds1.ItemNumber = ds2.ItemNumber'
print @SQLa
exec sp_executesql @SQLa
While @iLoopControl <= (SELECT MAX(ID)FROM #filters)
BEGIN
SELECT @par = FFDataType,
@f2 = FilterField,
@f3 = FilterValue
FROM #filters
WHERE ID = @iLoopControl;
SELECT
@filter = @filter + filter
,@errorMessage = @errorMessage + ' ' + errormessage
FROM
master.dbo.DynamicSQlFilter2(@par,@f2,@f3)
SELECT @iLoopControl = @iLoopControl + 1
TRUNCATE TABLE #resultSET
END
@SQLa will fill the temp table #filters with the filterfields, their datatype and their value to be filtered upon.
Now the loop will run through each record and pass this to the DynamicSQlFilter2 with @par being the datatype, @f2 being the column and @f3 the filtervalue.
DynamicSQlFilter2 will return me a table containing filter and errormessage, and that for each row in the filters temp table.
Better would be to get back everything at once and removing the need for the loop.
But how to do this and there I need help
To transform the dynamicSQl2 from only working row per row to be able to process the entire table #filters.
Lets look at part of the dynamicSQlFilter2 function
IF LOWER(@par2) IN ('int','float')
BEGIN
IF @f3 LIKE '%[a-zA-Z]%' AND LEN(@f3) <> 0 --AND @f3 <> null
BEGIN
SET @errorMessage = @errorMessage + 'Incorrect input passed (' + @f3 + '), contains characters.'
SET @parameter = @parameter
END
ELSE IF @f3 NOT LIKE '%[a-zA-Z]%' AND LEN(@f3) <> 0-- AND @f3 <> null
BEGIN
IF dbo.IsGetal(@f3) != 0 AND CHARINDEX(',',@f3) = 0 AND CHARINDEX('<',@f3) = 0 AND CHARINDEX('>',@f3) = 0 AND CHARINDEX('=',@f3) = 0 AND CHARINDEX('-',@f3) = 0 AND @line = 0
BEGIN
SET @parameter = @parameter + ' AND ' + @f2 + ' = ' + CAST(@f3 as nvarchar(max))
SET @errorMessage = @errorMessage
END
This part will check when the datatype is numeric like (int or float) it will check if the filtervalue contains characters and if not there several posibilities.
Here we just have a single number we want our filter column to be equal to, but other posibilities exist to like 1-3 means in our logic between 1 and 3 (Similar when dealing with dates the '&' must be translated into between date1 and date 2.
I need a way to both validate the use of these special characters and remove the need for RBAR or looping.
December 16, 2010 at 6:10 am
Those that have looked at this thank you.
A quick test on my part
DECLARE @SQLa nvarchar(max)
DECLARE @ffd varchar(1)
DECLARE @fvd varchar(1)
DECLARE @s1 nvarchar(max)
DECLARE @t2 nvarchar(max)
DECLARE @FilterFields nvarchar(max)
DECLARE @Filter_Values nvarchar(max)
DECLARE @Database nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE @j-2 nvarchar(max)
DECLARE @k nvarchar(max)
SET @ffd = ','
SET @fvd = ';'
SET @j-2 = ''
SET @k = ''
SET @SQLa = ''
SET @Database = 'Test'
SET @Table = 'dbo.Machine'
SET @FilterFields = 'MachineId,CompanyId,Value'
SET @Filter_Values = '<=1;A;1-5'
SET @s1 = LEFT(@Table,CHARINDEX('.',@Table) -1)
SET @t2 = RIGHT(@Table,((LEN(@Table)-CHARINDEX('.',@Table))))
CREATE TABLE #filters (ID int identity(1,1),FilterField varchar(max),FFDataType varchar(30), FilterValue varchar(max))
SET @SQLa = 'INSERT INTO #filters '
SET @SQLa = @SQLa + 'SELECT ds1.Item As FilterField,'
SET @SQLa = @SQLa + '(SELECT IFS.DATA_TYPE FROM ' + @Database + '.INFORMATION_SCHEMA.COLUMNS IFS WHERE IFS.COLUMN_NAME = ds1.Item'
SET @SQLa = @SQLa + ' AND IFS.TABLE_NAME = ''' + @t2 + ''' AND IFS.TABLE_SCHEMA = ''' + @s1 + ''') As FFDataType,ds2.Item As FilterValue'
SET @SQLa = @SQLa + ' FROM [dbo].[DelimitedSplit8K] (''' + @FilterFields + ''',''' + @ffd +''') ds1'
SET @SQLa = @SQLa + ' FULL OUTER JOIN [dbo].[DelimitedSplit8K] ('''+ @Filter_Values +''',''' + @fvd +''') ds2 on ds1.ItemNumber = ds2.ItemNumber'
--print @Filter_Values
exec sp_executesql @SQLa
select * from #filters
SELECT @j-2 = @j-2 + (select filter from dbo.DynamicSQlFilter2(FFDataType,FilterField,FilterValue))
FROM #filters ORDER BY ID
SELECT @k = @k + (select errorMessage from dbo.DynamicSQlFilter2(FFDataType,FilterField,FilterValue))
FROM #filters ORDER BY ID
print @j-2
print @k
drop table #filters
With result
AND MachineId <=1 AND Value BETWEEN '1' AND '5'
Incorrect input passed (A), contains characters.
Has shown that the loop is completely unnecessary and it works fine without changing a thing.
December 17, 2010 at 1:17 am
This little project has been terminated since we'll be getting new projects in on Monday.
I'll be posting the entire code and files later today, so that those that are interested in it can work on it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply