December 13, 2010 at 1:15 am
A new week has started but the problem still needs to be resolved if even possible.
December 13, 2010 at 2:24 am
Problem? I inferred from your previous post that it was resolved. Please let us know what the outstanding issues are.
John
December 13, 2010 at 3:38 am
The following code is then how the new build for filter
use Test
declare @ff nvarchar(max)
declare @fv nvarchar(max)
declare @ffd varchar(1)
declare @fvd varchar(1)
declare @table nvarchar(max)
declare @ts nvarchar(max)
declare @iLoopControl int
declare @iNextRowId int
declare @iCurrentRowId int
declare @par nvarchar(max), @f2 nvarchar(max),@f3 nvarchar(max), @filter nvarchar(max), @errorMessage nvarchar(max)
set @ff = 'MachineId,CompanyId,Description1'
set @fv = '<>109;<=100;;C'
set @ffd = ','
set @fvd = ';'
set @table = 'Machine'
set @ts = 'dbo'
set @iLoopControl = 1
set @par = ''
set @f2 = ''
set @f3 = ''
set @filter = ''
set @errorMessage = ''
create table #filters (ID int identity(1,1),FilterField varchar(max),FFDataType varchar(30), FilterValue varchar(max))
CREATE TABLE #resultSet(filter nvarchar(max),errorMessage nvarchar(max))
insert into #filters
select ds1.Item As FilterField,
(select IFS.DATA_TYPE from Test.INFORMATION_SCHEMA.COLUMNS IFS where IFS.COLUMN_NAME = ds1.Item
and IFS.TABLE_NAME = @table
and IFS.TABLE_SCHEMA = @ts) As FFDataType,ds2.Item As FilterValue
from [Test].[dbo].[DelimitedSplit8K] (@ff,@ffd) ds1
full outer join [Test].[dbo].[DelimitedSplit8K] (@fv,@fvd) ds2 on ds1.ItemNumber = ds2.ItemNumber
While @iLoopControl <= (select MAX(ID)from #filters)
BEGIN
select @par = FFDataType,
@f2 = FilterField,
@f3 = FilterValue
from #filters
where ID = @iLoopControl;
select @iLoopControl = @iLoopControl + 1
INSERT INTO #resultSet(filter,errorMessage)
SELECT filter, errormessage FROM master.dbo.DynamicSQlFilter2(@par,@f2,@f3)
SET @filter = @filter + (SELECT filter FROM #resultSet)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSet)
TRUNCATE TABLE #resultSet
END
print @Filter
print @errorMessage
truncate table #filters
drop table #filters
truncate table #resultSet
drop table #resultSet
Is a more elegant version of what my original stored procedure did for the filter, with this code I no longer need to substring and see if the results were any of 16 possible cases.
Now I get a table with the FilterFiled, its datatype and the given Filter Parameter value, I can run through this table and let my udf DynamicFilter generate the right filter and/or error messages for each given FilterField/FilterParameter (The WHERE part of the dynamic SQL at the end of the stored procedure).
Now if I use the udf DelimitedSplit8K on the selected columns, I will get something similar.
A table is generated where each new record represents a selected column, I can join this then with the information schema to find each fields dataype.
First problem I have to resolve here is how will I put the columns in the dynamic sql at the end with their proper name (As Col1 to ColX) without looping through the temp table (If I even need a temp table)
Second problem would be to calculate the size in Kb of the selected columns.
Last would be to cap the size so that when it goes over this cap we will only get back a dataset the size of the cap (minus off course the records that might be spread over the cap limit) and if the selected dataset is smaller just return the dataset.
Maybe I should make new topics to separate the problems from each other and get more feedback on the individual subjects.
December 13, 2010 at 3:51 am
I think I answered the first problem in the 18th message in this thread.
As for calculating the size of the result set, build a table of all the possible data types and the size in bytes of each one. If you've got lots of varchar/nvarchar columns then this will present you with a problem - you'll need to write some logic to determine the average size of each column based on what you know about the data within it. An alternative to that is to use the DATALENGTH function to calculate the size of each value in the result set and cut the result set off at a certain size. The problem with the latter is that you're then doing an awful lot of additional processing for what may only be a small benefit in terms of reducing the traffic across your network.
John
December 13, 2010 at 5:15 am
Yes I did figure it out
use Test
declare @SelectedColumns nvarchar(max)
declare @SelectStatement nvarchar(max)
set @SelectStatement = ''
set @SelectedColumns = 'MachineId, CompanyId, UtcTime, Value, Description1'
set @SelectedColumns = REPLACE(@SelectedColumns,' ','')
create table #MyColumns(ID int identity(1,1),ColumnName nvarchar(max))
insert into #MyColumns(ColumnName)
select ds.Item from Test.dbo.DelimitedSplit8K(@SelectedColumns,',') ds
SELECT @SelectStatement = 'SELECT '
SELECT @SelectStatement = @SelectStatement + '''' + ColumnName + ''' As Col' + cast(ID as nvarchar(5)) +', ' + ColumnName + ' As Col' + cast(ID as nvarchar(5)) + ','
FROM #MyColumns ORDER BY ID
SELECT @SelectStatement = LEFT(@SelectStatement,LEN(@SelectStatement)-1) -- Remove trailing comma
drop table #MyColumns
print @SelectStatement
Gives me the following result
SELECT 'MachineId' As Col1, MachineId As Col1,'CompanyId' As Col2, CompanyId As Col2,'UtcTime' As Col3, UtcTime As Col3,'Value' As Col4, Value As Col4,'Description1' As Col5, Description1 As Col5
Which is what I want for my select part of my dynamic S
On to the second and thridth, implementing a table that keeps the size of each datatype is going to be difucult cause
A)Wherer do I place this table
B)Portability (if the table is not present how do I resolve, I could write a stored procedure to make the table)
The question that should be asked then what is the best way to calculate the size of a dataset of a dynamic sql without using datalength.
In order to resolve the averages one would have to make another select instruction of the set you want.
I'll be making a new topic to get more feedback, with a concrete scenario
December 13, 2010 at 5:38 am
Place it wherever is most apposite. I think I would use the master database. If it's not there then your procedure's going to fail, unless you define it with a CTE or temp table within the proc, which I wouldn't recommend.
If you don't want to use DATALENGTH then you need to know something about your data in order to calculate the averages.
I'll look out for your new thread.
John
December 15, 2010 at 5:13 am
The new stored procedure, a whole lot more elegant and readable then the last version.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DynamicSQL3] Script Date: 12/15/2010 10:26:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DynamicSQL3]
@SelectedColumns nvarchar(max),
@Database nvarchar(max),
@Table nvarchar(max),
@Rows integer,
@FilterFields nvarchar(max),
@Filter_Values nvarchar(max)
AS
--DECLARE Block
DECLARE @SQL1 nvarchar(max)
DECLARE @SQLa nvarchar(max)
DECLARE @SelectStatement nvarchar(max)
DECLARE @WhereStatement nvarchar(max)
DECLARE @ffd varchar(1)
DECLARE @fvd varchar(1)
DECLARE @iLoopControl int
DECLARE @iNextRowId int
DECLARE @iCurrentRowId int
DECLARE @ts nvarchar(max)
DECLARE @par nvarchar(max)
DECLARE @f2 nvarchar(max)
DECLARE @f3 nvarchar(max)
DECLARE @filter nvarchar(max)
DECLARE @errorMessage nvarchar(max)
DECLARE @s1 nvarchar(max)
DECLARE @t2 nvarchar(max)
DECLARE @debug nvarchar(max)
--SET BLOCK
SET @SelectStatement = ''
SET @WhereStatement = ' WHERE 1 = 1 '
SET @SelectedColumns = REPLACE(REPLACE(@SelectedColumns,' ',''),';','')
SET @s1 = LEFT(@Table,CHARINDEX('.',@Table) -1)
SET @t2 = RIGHT(@Table,((LEN(@Table)-CHARINDEX('.',@Table))))
SET @ffd = ','
SET @fvd = ';'
SET @iLoopControl = 1
SET @par = ''
SET @f2 = ''
SET @f3 = ''
SET @filter = ''
SET @errorMessage = ''
--LOGIC BLOCK
-- Rows provided
IF @Rows = 0 OR @Rows IS NULL
BEGIN
SET @SelectStatement = 'SELECT '
END
ELSE
BEGIN
SET @SelectStatement = 'SELECT TOP ' + CAST(@Rows As Nvarchar(max)) + ' '
END
-- Build SELECT
CREATE TABLE #MyColumns(ID int identity(1,1),ColumnName nvarchar(max))
INSERT INTO #MyColumns(ColumnName)
SELECT ds.Item FROM dbo.DelimitedSplit8K(@SelectedColumns,',') ds
SELECT @SelectStatement = @SelectStatement + '''' + ColumnName + ''' As Col' + cast(ID as nvarchar(5)) +', ' + ColumnName + ' As Col' + cast(ID as nvarchar(5)) + ','
FROM #MyColumns ORDER BY ID
SELECT @SelectStatement = LEFT(@SelectStatement,LEN(@SelectStatement)-1) -- Remove trailing comma
-- Build WHERE
CREATE TABLE #filters (ID int identity(1,1),FilterField varchar(max),FFDataType varchar(30), FilterValue varchar(max))
CREATE TABLE #resultSET(filter nvarchar(max),errorMessage nvarchar(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'
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 @iLoopControl = @iLoopControl + 1
INSERT INTO #resultSET(filter,errorMessage)
SELECT filter, errormessage FROM master.dbo.DynamicSQlFilter2(@par,@f2,@f3)
SET @filter = @filter + (SELECT filter FROM #resultSET)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSET)
TRUNCATE TABLE #resultSET
END
--Final Changes To Select & Where
SET @SelectStatement = @SelectStatement + ',''' + @errorMessage + '''As EM1'
SET @WhereStatement = @WhereStatement + @filter
--Final SQL Statement
SET @SQL1 = @SelectStatement + ' FROM ' + @Database + '.' + @Table + @WhereStatement
--print @SQL1
exec sp_executesql @SQL1
TRUNCATE TABLE #MyColumns
TRUNCATE TABLE #filters
TRUNCATE TABLE #resultSET
DROP TABLE #MyColumns
DROP TABLE #filters
DROP TABLE #resultSET
December 15, 2010 at 7:41 am
You've still got that unnecessary WHILE loop in there.
Even if you insist on keeping the loop, the #resultSET table isn't needed - you can just do this:
SELECT
@filter = @filter + filter
,@errorMessage = @errorMessage + ' ' + errormessage
FROM
master.dbo.DynamicSQlFilter2(@par,@f2,@f3)
Can the DynamicSQlFilter2 function ever return more than one row?
I notice you've used a FULL OUTER JOIN for your filter fields and filter values. You need to include some logic to handle the case where the result of that contains NULLs, which will be when the number of filter fields is not equal to the number of filter values.
You will need to select the following additional columns from information_schema.columns in order to determine the size of your data: CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE.
John
December 15, 2010 at 8:09 am
no the dynamicsqlfilter2 can never return more then 1 column
Since it just does the following
What's the datatype passed (@par)
Is @f3 which would be the filtervalue correct for the passed datatype (Which usually means in case of numeric datatypes is it a correct number, in case of date is it a correct date)
Based upon this either a filter clause is build
Pex: int, MachineId, <>12
Will give: AND MachineId <> 12
When the filtervalue is not correct an error Message will be passed
Pex: int,MachineId, ABC
Will give: Incorrect input passed (ABC), contains characters.
While the while loop isn't practical, row by a row, I have to leave it in since thats the way the dynamicsqlfilter2 works atm. It could be reworked.
December 16, 2010 at 1:24 am
Just tested it, and the null doesn't even cause problems.
DECLARE @rc int
DECLARE @SelectedColumns nvarchar(max)
DECLARE @Database nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE @Rows int
DECLARE @FilterFields nvarchar(max)
DECLARE @Filter_Values nvarchar(max)
-- TODO: Set parameter values here.
EXECUTE @rc = [master].[dbo].[DynamicSQL3]
'MachineId, CompanyId, UtcTime, Value, Description1'
,'Test'
,'dbo.Machine'
,0
,'MachineId'
,'1;1;1'
GO
Will execute the following SQL instruction near the end
SELECT 'MachineId' As Col1, MachineId As Col1,'CompanyId' As Col2, CompanyId As Col2,'UtcTime' As Col3, UtcTime As Col3,'Value' As Col4, Value As Col4,'Description1' As Col5, Description1 As Col5,' 'As EM1 FROM Test.dbo.Machine WHERE 1 = 1 AND MachineId = 1
And this
DECLARE @rc int
DECLARE @SelectedColumns nvarchar(max)
DECLARE @Database nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE @Rows int
DECLARE @FilterFields nvarchar(max)
DECLARE @Filter_Values nvarchar(max)
-- TODO: Set parameter values here.
EXECUTE @rc = [master].[dbo].[DynamicSQL3]
'MachineId, CompanyId, UtcTime, Value, Description1'
,'Test'
,'dbo.Machine'
,0
,'MachineId,,Value'
,'1;1;1'
GO
will give this
SELECT 'MachineId' As Col1, MachineId As Col1,'CompanyId' As Col2, CompanyId As Col2,'UtcTime' As Col3, UtcTime As Col3,'Value' As Col4, Value As Col4,'Description1' As Col5, Description1 As Col5,' 'As EM1 FROM Test.dbo.Machine WHERE 1 = 1 AND MachineId = 1 AND Value = 1
Now the loop can be avoided if the dynamicSQLFilter could return a table, but how to do this and at the mean time do validations is unclear(The attached zip holds the dynamicSQLFilter function)
December 16, 2010 at 6:08 am
Did a quick test
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
Returns
AND MachineId <=1 AND Value BETWEEN '1' AND '5'
Incorrect input passed (A), contains characters.
So I can drop the while loop
OK, tested it without the while loop seems to work
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 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply