December 8, 2010 at 2:50 am
The idea is to get the amount of kb the resultset/dataset is and put a limit on its size
Hello,
I got a stored procedure that builds a dynamic sql, based on parameters passed along, executes this sql and returns this result to the user.
The resulting dataset can however be to large in size to be of any use, so I've been wondering if there's a way to limit the size of the dataset.
Any help would be appreciated.
EDIT
Some progress have been made but input is still needed.
What has been done so far:
*delimeter udf
*getting datatypes through INFORMATION_SCHEMA.COLUMNS
December 8, 2010 at 2:58 am
What do you mean by size? Do you mean number of rows? If so, use the TOP keyword. If you mean number of KB, I think it's possible, depending on the data types of the columns being returned.
John
December 8, 2010 at 3:05 am
Yeah I should clarify a bit, the parameters for the stored procedure are: Selected Fields, Table, Database, number of rows,filter fields and filter values.
If the number of rows is 0,'' or null all rows are returned.
So I need to limit the size of the dataset in kb/mb size
December 8, 2010 at 3:09 am
OK, so it depends on the data types of the columns returned, not of the parameters. Please post your code and table DDL so that we can help you.
John
December 8, 2010 at 3:22 am
OK First is dbo.DynamicSQL2
Please note its a work in progress so if you things that could be better done, please note but I'm also working on it.
DynamicSQL2 builds the dynamic SQL to be used, this sp can give you a dataset back on any table in any database present on the current server.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DynamicSQL2] Script Date: 12/08/2010 11:13:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[DynamicSQL2]
@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 @counter integer
DECLARE @counter2 integer
DECLARE @idx integer
DECLARE @idx2 integer
DECLARE @idx3 integer
DECLARE @lidx2 integer
DECLARE @lidx3 integer
DECLARE @lengthSelectedColumns2 integer
DECLARE @truncatedChar nvarchar(max)
DECLARE @occurenceChar integer
DECLARE @truncatedChar2 nvarchar(max)
DECLARE @occurenceChar2 integer
DECLARE @currentField nvarchar(max)
DECLARE @currentParameter nvarchar(max)
DECLARE @truncatedChar3 nvarchar(max)
DECLARE @occurenceChar3 integer
DECLARE @f1 nvarchar(max)
DECLARE @f2 nvarchar(max)
DECLARE @f3 nvarchar(max)
DECLARE @par nvarchar(max)
DECLARE @par2 nvarchar(max)
DECLARE @parameter nvarchar(max)
DECLARE @parameter2 nvarchar(max)
DECLARE @index integer
DECLARE @index2 integer
DECLARE @index3 integer
CREATE TABLE #dType(dataType nvarchar(max))
CREATE TABLE #resultSet(filter nvarchar(max),errorMessage nvarchar(max))
DECLARE @s1 nvarchar(max)
DECLARE @t2 nvarchar(max)
DECLARE @filter nvarchar(max)
DECLARE @errorMessage nvarchar(max)
DECLARE @em1 nvarchar(max)
--SET Block
/*The user has to give the table in as schemaname.tablename,so we need to separate these to be able to check vs the master data)
SET @s1 = LEFT(@Table,CHARINDEX('.',@Table) -1)
SET @t2 = RIGHT(@Table,((LEN(@Table)-CHARINDEX('.',@Table))))
SET @counter = 0
SET @counter2 = 0
SET @lengthSelectedColumns2 = LEN(@SelectedColumns)
SET @SelectedColumns = REPLACE(@SelectedColumns,';',',')
SET @SelectedColumns = REPLACE(@SelectedColumns,' ','')
SET @FilterFields = REPLACE(@FilterFields,';',',')
SET @Filter_Values = REPLACE(@Filter_Values,'''','')
SET @truncatedChar = REPLACE(@SelectedColumns,',','')
SET @occurenceChar = (LEN(@SelectedColumns) - LEN(@truncatedChar))
SET @truncatedChar2 = REPLACE(@FilterFields,',','')
SET @occurenceChar2 = (LEN(@FilterFields) - LEN(@truncatedChar2))
SET @truncatedChar3 = REPLACE(@Filter_Values,';','')
SET @occurenceChar3 = (LEN(@Filter_Values) - LEN(@truncatedChar3))
SET @idx2 = 0
SET @idx3 = 0
SET @f1 = ''
SET @f2 = ''
SET @parameter = ' WHERE 1 = 1 '
SET @filter = ''
SET @errorMessage = ''
SET @em1 = ''
IF @Filter_Values <> '' AND LEN(@truncatedChar3) <> 0
BEGIN
WHILE (@counter2 < @occurenceChar2 + 1)
BEGIN
SET @counter2 = @counter2 + 1
SET @idx2 = CHARINDEX(',',@FilterFields)
SET @idx3 = CHARINDEX(';',@Filter_Values)
IF @idx2 <> 0
BEGIN
SET @lidx2 = LEN(REPLACE(left(@FilterFields,@idx2 - 1),' ',''))
END
ELSE
BEGIN
SET @lidx2 = LEN(@FilterFields)
END
IF @idx3 <> 0
BEGIN
SET @lidx3 = LEN(REPLACE(left(@Filter_Values,@idx3 - 1),' ',''))
END
ELSE
BEGIN
SET @lidx3 = LEN(@Filter_Values)
END
print 'FF: ' + @FilterFields
print 'FV: ' + @Filter_Values
print 'ID2: ' + cast(@idx2 as varchar(10))
print 'ID3: ' + cast(@idx3 as varchar(10))
print 'LD2: ' + cast(@lidx2 as varchar(10))
print 'LD3: ' + cast(@lidx3 as varchar(10))
print ''
/*
The Block that follows with all the cases is a way to pick up of the correct number of filter fields are given.
For this it looks at 4 things
*)Are there more fields, there separated by a ','
*)Is the length of the fields different from 0, so is there still a field filled.
*)Are there more filter values, separated by ';'
*)Is the length of filter values different from, so is there still a filter-value filled.
*/
IF((@idx2) = 0 AND (@lidx2) = 0) AND ((@idx3) = 0 AND (@lidx3) = 0)
BEGIN
print 'Case 1'
print ''
SET @errorMessage = 'No filters given.'
break
END
ELSE IF((@idx2) = 0 AND (@lidx2) = 0) AND ((@idx3) = 0 AND (@lidx3) != 0)
BEGIN
print 'Case 2'
print ''
SET @errorMessage = 'No filters given.'
break
END
ELSE IF((@idx2) = 0 AND (@lidx2) = 0) AND ((@idx3) != 0 AND (@lidx3) = 0)
BEGIN
print 'Case 3'
print ''
SET @errorMessage = 'No filters given'
END
ELSE IF((@idx2) = 0 AND (@lidx2) = 0) AND ((@idx3) != 0 AND (@lidx3) != 0)
BEGIN
print 'Case 4'
print ''
SET @errorMessage = 'No filters given.'
break
END
ELSE IF((@idx2) = 0 AND (@lidx2) != 0) AND ((@idx3) = 0 AND (@lidx3) = 0)
BEGIN
print 'Case 5'
print ''
SET @f2 = REPLACE(@FilterFields,' ','')
SET @f3 = REPLACE(@Filter_Values,' ','')
SET @errorMessage = 'No filter value given for field : ' + @f2 + '.'
END
ELSE IF((@idx2) = 0 AND (@lidx2) != 0) AND ((@idx3) = 0 AND (@lidx3) != 0)
BEGIN
print 'Case 6'
print ''
SET @f2 = REPLACE(@FilterFields,' ','')
SET @f3 = REPLACE(@Filter_Values,' ','')
SET @par = 'INSERT INTO #dType SELECT data_type FROM ' + @Database + '.information_schema.columns WHERE table_schema = ''' + @s1 + ''' AND table_name = ''' + @t2 + ''' AND column_name = ''' + @f2 + ''''
EXEC(@par)
SET @par2 = (SELECT * FROM #dType)
TRUNCATE TABLE #dType
INSERT INTO #resultSet(filter,errorMessage)
SELECT filter, errormessage FROM dbo.DynamicSQlFilter2(@par2,@f2,@f3)
SET @filter = (SELECT filter FROM #resultSet)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSet)
TRUNCATE TABLE #resultSet
SET @parameter = @parameter + @filter
break
END
ELSE IF((@idx2) = 0 AND (@lidx2) != 0) AND ((@idx3) != 0 AND (@lidx3) = 0)
BEGIN
print 'Case 7'
print ''
SET @errorMessage = 'No filters given'
END
ELSE IF((@idx2) = 0 AND (@lidx2) != 0) AND ((@idx3) != 0 AND (@lidx3) != 0)
BEGIN
print 'Case 8'
print ''
SET @f2 = REPLACE(@FilterFields,' ','')
SET @f3 = REPLACE(@Filter_Values,' ','')
SET @par = 'INSERT INTO #dType SELECT data_type FROM ' + @Database + '.information_schema.columns WHERE table_schema = ''' + @s1 + ''' AND table_name = ''' + @t2 + ''' AND column_name = ''' + @f2 + ''''
EXEC(@par)
SET @par2 = (SELECT * FROM #dType)
TRUNCATE TABLE #dType
INSERT INTO #resultSet(filter,errorMessage)
SELECT filter, errormessage FROM dbo.DynamicSQlFilter2(@par2,@f2,@f3)
SET @filter = (SELECT filter FROM #resultSet)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSet)
TRUNCATE TABLE #resultSet
SET @parameter = @parameter + @filter
break
END
ELSE IF((@idx2) != 0 AND (@lidx2) = 0) AND ((@idx3) = 0 AND (@lidx3) = 0)
BEGIN
print 'Case 9'
print ''
SET @errorMessage = 'No filters given'
END
ELSE IF((@idx2) != 0 AND (@lidx2) = 0) AND ((@idx3) = 0 AND (@lidx3) != 0)
BEGIN
print 'Case 10'
print ''
SET @errorMessage = 'No filters given.'
break
END
ELSE IF((@idx2) != 0 AND (@lidx2) = 0) AND ((@idx3) != 0 AND (@lidx3) = 0)
BEGIN
print 'Case 11'
print ''
SET @errorMessage = 'No filters given.'
END
ELSE IF((@idx2) != 0 AND (@lidx2) = 0) AND ((@idx3) != 0 AND (@lidx3) != 0)
BEGIN
print 'Case 12'
print ''
SET @errorMessage = 'No filters given.'
break
END
ELSE IF((@idx2) != 0 AND (@lidx2) != 0) AND ((@idx3) = 0 AND (@lidx3) = 0)
BEGIN
print 'Case 13'
print ''
SET @errorMessage = 'No more filters values given.'
break
END
ELSE IF((@idx2) != 0 AND (@lidx2) != 0) AND ((@idx3) = 0 AND (@lidx3) != 0)
BEGIN
print 'Case 14'
print ''
SET @f2 = REPLACE(left(@FilterFields,@idx2 - 1),' ','')
SET @f3 = REPLACE(@Filter_Values,' ','')
print 'F2: ' + @f2
print 'F3: ' + @f3
SET @par = 'INSERT INTO #dType SELECT data_type FROM ' + @Database + '.information_schema.columns WHERE table_schema = ''' + @s1 + ''' AND table_name = ''' + @t2 + ''' AND column_name = ''' + @f2 + ''''
EXEC(@par)
SET @par2 = (SELECT * FROM #dType)
TRUNCATE TABLE #dType
INSERT INTO #resultSet(filter,errorMessage)
SELECT filter, errormessage FROM dbo.DynamicSQlFilter2(@par2,@f2,@f3)
SET @filter = (SELECT filter FROM #resultSet)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSet)
TRUNCATE TABLE #resultSet
SET @parameter = @parameter + @filter
SET @FilterFields = SUBSTRING(@FilterFields,@idx2 + 1,LEN(@FilterFields))
SET @Filter_Values = ''
END
ELSE IF((@idx2) != 0 AND (@lidx2) != 0) AND ((@idx3) != 0 AND (@lidx3)= 0)
BEGIN
print 'Case 15'
print ''
SET @f2 = REPLACE(left(@FilterFields,@idx2 - 1),' ','')
SET @f3 = REPLACE(left(@Filter_Values,@idx3 - 1),' ','')
SET @errorMessage = 'No filter value given for field : ' + @f2 + '.'
SET @FilterFields = SUBSTRING(@FilterFields,@idx2 + 1,LEN(@FilterFields))
SET @Filter_Values = SUBSTRING(@Filter_Values,@idx3 + 1,LEN(@Filter_Values))
END
ELSE IF((@idx2) != 0 AND (@lidx2) != 0) AND ((@idx3) != 0 AND (@lidx3) != 0)
BEGIN
print 'Case 16'
print ''
SET @f2 = REPLACE(left(@FilterFields,@idx2 - 1),' ','')
SET @f3 = REPLACE(left(@Filter_Values,@idx3 - 1),' ','')
SET @par = 'INSERT INTO #dType SELECT data_type FROM ' + @Database + '.information_schema.columns WHERE table_schema = ''' + @s1 + ''' AND table_name = ''' + @t2 + ''' AND column_name = ''' + @f2 + ''''
EXEC(@par)
SET @par2 = (SELECT * FROM #dType)
TRUNCATE TABLE #dType
INSERT INTO #resultSet(filter,errorMessage)
SELECT filter, errormessage FROM dbo.DynamicSQlFilter2(@par2,@f2,@f3)
SET @filter = (SELECT filter FROM #resultSet)
SET @errorMessage = @errorMessage + ' ' +(SELECT errorMessage FROM #resultSet)
TRUNCATE TABLE #resultSet
SET @parameter = @parameter + @filter
SET @FilterFields = SUBSTRING(@FilterFields,@idx2 + 1,LEN(@FilterFields))
SET @Filter_Values = SUBSTRING(@Filter_Values,@idx3 + 1,LEN(@Filter_Values))
END
END
END
ELSE IF @Filter_Values = '' OR LEN(@truncatedChar3) = 0
BEGIN
SET @errorMessage = @errorMessage + 'No filter values given.'
END
/* This block builds the dynamic sql base on the Selected Columns)
WHILE (@counter < @occurenceChar + 1)
BEGIN
SET @counter = @counter + 1
SET @idx = CHARINDEX(',',@SelectedColumns)
IF (@idx != 0)
BEGIN
SET @lengthSelectedColumns2 = LEN(@SelectedColumns)
SET @f1 = @f1 + '''' + left(@SelectedColumns,@idx - 1) + '''' + ' As Col' + CAST(@counter As nvarchar(3)) + 'A, ' + left(@SelectedColumns,@idx - 1) + ' As Col' + CAST(@counter As nvarchar(3)) + ', '
SET @index = (@lengthSelectedColumns2 - @idx)
SET @SelectedColumns = SUBSTRING(@SelectedColumns,@idx + 1,LEN(@SelectedColumns))
END
ELSE
BEGIN
SET @f1 = @f1 + '''' + @SelectedColumns + '''' + ' As Col' + CAST(@counter As nvarchar(3))+ 'A, ' + @SelectedColumns + ' As Col' + CAST(@counter As nvarchar(3))
break
END
END
IF @Rows = 0 OR @Rows IS NULL
BEGIN
SET @SQL1 = 'SELECT ' + @f1 + ', ''' + @errorMessage + ''' As EM1 FROM ' + @Database + '.' + @Table + @parameter
END
ELSE
BEGIN
SET @SQL1 = 'SELECT TOP ' + CAST(@Rows As nvarchar(10)) + ' ' + @f1 + ', ''' + @errorMessage + ''' As EM1 FROM ' + @Database + '.' + @Table + @parameter
END
print @SQL1
DROP TABLE #dType
DROP TABLE #resultSet
EXEC(@SQL1)
December 8, 2010 at 3:26 am
The udf dynamicSQLFilter2 will give the sp dynamicSQL2 the filter back along with an error message if needed. (Atm this function can pick up int,float,varchar and datetimes filters)
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[DynamicSQlFilter2] Script Date: 12/08/2010 11:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[DynamicSQlFilter2] (@par2 nvarchar(max),@f2 nvarchar(max),@f3 nvarchar(max))
RETURNS @filterTable TABLE(filter nvarchar(max),errorMessage nvarchar(max))
AS BEGIN
DECLARE @parameter nvarchar(max)
DECLARE @errorMessage nvarchar(max)
DECLARE @cid integer
DECLARE @lcid varchar(max)
DECLARE @rcid varchar(max)
DECLARE @line integer
DECLARE @amp integer
SET @parameter = ''
SET @errorMessage = ''
--SET @f3 = REPLACE(@f3,'''','')
SET @f3 = REPLACE(REPLACE(@f3,')',''),'(','')
SET @line = LEN(@f3) - LEN(REPLACE(@f3,'-',''))
SET @amp = LEN(@f3) - LEN(REPLACE(@f3,'&',''))
SET @parameter = ''
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
ELSE 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 + ' IN (' + CAST(@f3 as nvarchar(max)) + ')'
SET @errorMessage = @errorMessage
END
ELSE 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
ELSE 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
ELSE 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
ELSE 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
ELSE 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
ELSE 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
ELSE 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
ELSE 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 = 1
BEGIN
SET @cid = CHARINDEX('-',@f3)
SET @lcid = REPLACE(LEFT(@f3,@cid - 1),' ','')
SET @rcid = SUBSTRING(@f3,@cid+1,len(@f3))
SET @parameter = @parameter + ' AND ' + @f2 + ' BETWEEN ''' + cast(@lcid as nvarchar(max)) + ''' AND ''' + cast(@rcid as nvarchar(max)) +''''
END
ELSE 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 > 1
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'Incorrect syntax passed (' + @f3 + '), only 1 ''-'' allowed.'
END
ELSE
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'Incorrect number passed (' + @f3 + ').'
END
END
ELSE
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'No filter value given for: ' + @f2 + '.'
END
END
ELSE IF LOWER(@par2) IN ('varchar','nvarchar')
BEGIN
IF @f2 IS NOT NULL AND @f3 = ''
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'No filter value given for: ' + @f2 + '.'
END
ELSE
BEGIN
SET @parameter = @parameter + ' AND ' + @f2 + ' LIKE ''%' + CAST(@f3 as nvarchar(max)) + '%'''
END
END
ELSE If LOWER(@par2) IN ('datetime','smalldatetime')
BEGIN
IF CHARINDEX('&',@f3) != 0 AND @amp = 1
BEGIN
--SET @parameter = @parameter
SET @cid = CHARINDEX('&',@f3)
SET @lcid = REPLACE(LEFT(@f3,@cid - 1),' ','')
SET @rcid = SUBSTRING(@f3,@cid+1,len(@f3))
IF ISDATE(@lcid) = 1 AND ISDATE(@rcid) = 1
BEGIN
SET @parameter = @parameter + ' AND CAST(CONVERT(varchar,' + @f2 + ',101) AS '+ @par2 + ') BETWEEN CAST(CONVERT(varchar,''' + cast(@lcid as nvarchar(max)) + ''',101) AS '+ @par2 + ') AND CAST(CONVERT(varchar,''' + cast(@rcid as nvarchar(max)) +''',101) AS '+ @par2 + ')'
END
ELSE
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'Incorrect dates given: ' + @lcid + ' & ' + @rcid + '.'
END
END
ELSE IF CHARINDEX('&',@f3) = 0 AND @amp = 0
IF ISDATE(@f3) = 1
BEGIN
SET @parameter = @parameter + ' AND CAST(CONVERT(varchar,' + @f2 + ',101) AS '+ @par2 + ') = CAST(CONVERT(varchar,''' + @f3 + ''',101) AS '+ @par2 + ')'
END
ELSE
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'Incorrect date given: ' + @f3 + '.'
END
ELSE IF CHARINDEX('&',@f3) != 0 AND @amp > 1
BEGIN
SET @parameter = @parameter
SET @errorMessage = @errorMessage + 'Incorrect syntax passed (' + @f3 + '), only 1 ''&'' allowed.'
END
--SET @parameter = @parameter
END
--set @parameter = @parameter + ' ' + LOWER(@f2)
--SET @errorMessage = '1111'
INSERT INTO @filterTable(filter,errorMessage) VALUES (@parameter,@errorMessage)
RETURN
END
December 8, 2010 at 3:28 am
The udf IsGetal will check fo the filter function if a value is a real number, since we can get filters on fields with money stored in them we want to reject inputs like € which are considered numeric with the build in Numeric function
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[IsGetal] Script Date: 12/08/2010 11:26:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[IsGetal](@c nvarchar(max))
RETURNS bit
AS BEGIN
DECLARE @n bit
DECLARE @counter int
DECLARE @co int
DECLARE @cn varchar(20)
DECLARE @cnc int
SET @counter = 0
SET @cnc = 0
--SET @C = '1,2,3,4,5,6,7,8,9,a'
SET @n = 1
SET @C = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@c,'<',''),'>',''),'=',''),')',''),'(',''),'-',','),' ','')
SET @co = LEN(@c) - LEN(REPLACE(@c,',',''))
WHILE @counter <= @co
BEGIN
IF @n = 0
BEGIN
break
END
IF CHARINDEX(',',@c) <> 0
BEGIN
SET @cn = LEFT(@c,CHARINDEX(',',@c)-1)
SET @C = SUBSTRING(@c,CHARINDEX(',',@c) + 1,LEN(@c))
END
ELSE
BEGIN
SET @cn = @C
END
IF LEN(@cn) <> 0
BEGIN
SET @n =
CASE
WHEN @cn NOT LIKE '%[^0-9]%'
THEN 1
ELSE 0
END
END
ELSE
BEGIN
SET @n = 0
END
IF @cn = '' OR @cn IS NULL
BEGIN
SET @n = 0
break
END
SET @counter = @counter + 1
END
RETURN @n
END
December 8, 2010 at 3:33 am
Example on Adventureworks
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].[DynamicSQL2]
'ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate'
,'AdventureWorks'
,'Person.Contact'
,15
,'ContactID'
,'<>1'
GO
The fields Col1A ... ColXA are the headers for the fields (this can be better off course)
December 8, 2010 at 3:33 am
I can't find where you're setting the value of @SelectedColumns. This is the important part. You need to get the data types of the columns you are returning in order to determine (or, in some instances, estimate) the size of the result set. If the data types are such as varchar(max) then you are also going to need to know something about your data.
John
December 8, 2010 at 3:38 am
The @SelectedColumns are passed along to the stored procedure
In the example
'ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate'
Are the columns I want to select, the following lines both replace ';' and ' ' spaces in the selected columns.
SET @SelectedColumns = REPLACE(@SelectedColumns,';',',')
SET @SelectedColumns = REPLACE(@SelectedColumns,' ','')
The following
SET @par = 'INSERT INTO #dType SELECT data_type FROM ' + @Database + '.information_schema.columns WHERE table_schema = ''' + @s1 + ''' AND table_name = ''' + @t2 + ''' AND column_name = ''' + @f2 + ''''
EXEC(@par)
SET @par2 = (SELECT * FROM #dType)
TRUNCATE TABLE #dType
Will get me the datatype of the current field, this is used to determine the datype of filter field
December 8, 2010 at 3:47 am
Ah yes, so they are. Please have a read of this...
www.sommarskog.se/dynamic_sql.html
... and decide whether you're still happy with this way of coding. If you are, then what you need is a function to split @SelectedColumns into its individual components (search this site for how to do that), and then you need to use INFORMATION_SCHEMA.COLUMNS to determine the data type of each column. If the columns are of fixed-width types such as int or char(n) then you'll be able to calculate the precise size of each row of the result set in bytes. If you have types such as varchar(n) then you'll be able to calculate the maximum size of each row. If you have types such as varchar(max), there is no limit to the potential size of the rows, and you'll need to build in some logic to handle that.
Good luck, and post back if there's anything you're still not clear on.
John
December 8, 2010 at 7:36 am
Server Error
404 - File or directory not found.
The resource you are looking for might have been removed, had its name changed, or is temporarily unavailable.
Is what i get from that link
OK If i got to http://www.sommarskog.se and then to dynamic sql it works
December 8, 2010 at 7:42 am
Oh. Maybe it's been taken down for maintenance. If you do a Google search for it, you can look at the cached version.
John
December 8, 2010 at 8:23 am
Ok reading it now
Found also what went wrong when I clicked the link it went to http://www.sqlservercentral.com/Forums/www.sommarskog.se/dynamic_sql.html
I have read the areticle and seen some good idea's to prevent SQL injection but allso some major obstacles.
The article mentions never to pass table name and/or column names, there is no way around this in my application.
The application is report made with report server 2008, the user already gets all the database names and when he/she selects one he/she can select a table in that database.
And with a mvp select the columns he/she want.
As John said I looked on this website and found a function to split a string up based on a delimater,
so if i get for instance following selected colums 'ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate'
I can put these in a temp table and loop through them.
December 9, 2010 at 3:31 am
bump
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply