December 9, 2010 at 3:42 am
Resender (12/8/2010)
I can put these in a temp table and loop through them.
Shouldn't be any reason to do any looping. The splitting of the string can be done with a set-based query; likewise the reassembling of the column names in your SELECT statement. You'll also want to validate your column names paramater by checking for illegal characters and verifying that the columns actually exist, and, of course by taking any steps necessary to prevent SQL injection.
John
December 9, 2010 at 5:04 am
OK,
I believe set-based query to split my columns was already in my stored procedure
SET @truncatedChar = REPLACE(@SelectedColumns,',','')
SET @occurenceChar = (LEN(@SelectedColumns) - LEN(@truncatedChar))
...
WHILE (@counter < @occurenceChar + 1)
BEGIN
SET @counter = @counter + 1
SET @idx = CHARINDEX(',',@SelectedColumns)
...
SET @SelectedColumns = SUBSTRING(@SelectedColumns,@idx + 1,LEN(@SelectedColumns))
The function I found would return my parameter substringed in a table.
Ex:
'ID,MachineID,CompanyID'
would return to me as
ID ColumnName
1 ID
2 MachineID
3 CompanyID
I would then store that in a temp table and loop through that, off course that would be a lot more work (well maybe).
I can use the INFORMATION_SCHEMA.COLUMNS to check whether the columns exist, as for building the eventual SQL it would be handier to put the column names in the first record instead of doing the 'Column Name' As ColXA.
Still I would need to calculate the size of my dataset and put a limit on it.
December 9, 2010 at 6:06 am
Resender (12/9/2010)
OK,I believe set-based query to split my columns was already in my stored procedure
SET @truncatedChar = REPLACE(@SelectedColumns,',','')
SET @occurenceChar = (LEN(@SelectedColumns) - LEN(@truncatedChar))
...
WHILE (@counter < @occurenceChar + 1)
BEGIN
SET @counter = @counter + 1
SET @idx = CHARINDEX(',',@SelectedColumns)
...
SET @SelectedColumns = SUBSTRING(@SelectedColumns,@idx + 1,LEN(@SelectedColumns))
The function I found would return my parameter substringed in a table.
Ex:
'ID,MachineID,CompanyID'
would return to me as
ID ColumnName
1 ID
2 MachineID
3 CompanyID
I would then store that in a temp table and loop through that, off course that would be a lot more work (well maybe).
I can use the INFORMATION_SCHEMA.COLUMNS to check whether the columns exist, as for building the eventual SQL it would be handier to put the column names in the first record instead of doing the 'Column Name' As ColXA.
Still I would need to calculate the size of my dataset and put a limit on it.
If it's got a WHILE loop in it then it's not set-based. Once you've validated your columns, you can either build your SELECT statement directly from the input parameter, or you can do something like this:
SELECT @SelectStatement = 'SELECT '
SELECT @SelectStatement = @SelectStatement + ColumnName + ','
FROM MyColumns ORDER BY ID
SELECT @SelectStatement = LEFT(@SelectStatement,LEN(@SelectStatement)-1) -- Remove trailing comma
SELECT @SelectStatement = @SelectStatement + ' FROM MyTable WHERE....'
John
December 9, 2010 at 7:02 am
Ok from lookups on google I've learned that set-based will work on the entire set and not row by row.
For me this is quite a new concept, so I'm not grasping it.
My first problem is when I delimit my @SelectedColumns does this require a while loop.
Or how do I do it.
December 9, 2010 at 7:06 am
Resender (12/9/2010)
My first problem is when I delimit my @SelectedColumns does this require a while loop.Or how do I do it.
Are you talking about splitting the string, or reassembling it after you've done all the checks? Either way, you don't need a loop. Search this site for a function to split strings (I thought you said you'd already done that) or adapt the pseudo code I provided in my previous post.
John
December 9, 2010 at 7:56 am
I'll demonstrate the problem I having grasping the concept with an example
I have a database MachineRegistration, in this I have table machine.MRegistration.
This table has the following fields MachineId, RegisterId, UtcTime, Value; with the combo of MachineId,RegisterId,UtcTime allways unique and hence primairy key.
So lets say I want to select all the fields (MachineId, RegisterId, UtcTime, Value).
Currently this would get passed 'MachineId, RegisterId, UtcTime, Value', 'MachineRegistration','dbo.MRegistration'.
So the function I found will indeed split but return the result in form of a table, so 'MachineId, RegisterId, UtcTime, Value'
will turn in a table that looks like this
ID ColumnName
1 MachineId
2 RegisterId
3 UtcTime
4 Value
Ok I realised I can do the following
SELECT ds1.Item As ColumName,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM [Test].[dbo].[DelimitedSplit8K] (
'MachineId,CompanyId,CreatedUtcTime,MachineSerialNumber',
',') ds1
INNER JOIN MachineRegistration.INFORMATION_SCHEMA.COLUMNS IFS ON ds1.Item = IFS.COLUMN_NAME
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Machine'
GO
CompanyId dboMachineCompanyId YESint NULL
CreatedUtcTime dboMachineCreatedUtcTime YESdatetimeNULL
MachineId dboMachineMachineId NO int NULL
MachineSerialNumber dboMachineMachineSerialNumber NO varchar100
Problem with this is that I need to have MachineRegistration.INFORMATION_SCHEMA.COLUMNS
I could off course put that in a variable and execute the variable.
Then the problem would be to then calculate the size of the dataset from this (should I put this result in a temp table and then process)(Not to mention when I later have to add a top X to the query).
December 10, 2010 at 1:07 am
bump
December 10, 2010 at 1:31 am
Resender (12/9/2010)
Problem with this is that I need to have MachineRegistration.INFORMATION_SCHEMA.COLUMNSI could off course put that in a variable and execute the variable.
Then the problem would be to then calculate the size of the dataset from this (should I put this result in a temp table and then process)(Not to mention when I later have to add a top X to the query).
Sorry, I don't understand what you mean by "have" MachineRegistration.INFORMATION_SCHEMA.COLUMNS. Please could you elaborate.
As for calculating the dataset size, it really depends on the possible data types of the columns, as I explained in an earlier post. What are the data types?
John
December 10, 2010 at 2:08 am
OK so the function I found (DelimitedSplit8K)
returns me the delimited varchar as a table where each record is the next value
turns into
ID ColumnName
1 MachineId
2 RegisterId
3 UtcTime
4 Value
I can join this with the INFORMATION_SCHEMA.COLUMNS and I'll get the following:
CompanyId in table dbo.Machine, is NULL-able, of the datatype int
CreatedUtcTime in table dbo.Machine, is NULL-able, of the datatype datetime
MachineId in table dbo.Machine, is not NULL-able, of the datatype int
MachineSerialNumber in table dbo.Machine, is not NULL-able, of the datatype varchar with a maximum character length of 100.
Since the nature of what I want to achieve I want to execute this from the master systemdatabase, since this is
A)Always present on an SQL Server Installation
B)Contains all metadata
If I just join INFORMATION_SCHEMA.COLUMNS from the master database for columns in a table in the MachineRegistration database I will get zero results.
In order to get results I would need to join the INFORMATION_SCHEMA.COLUMNS from the database MachineRegistration.
Next up would be using that information to get the size of the dataset.
I could do the same for the filter fields and filtervalues I want to pass, let say I want all the MachineId <> 109 and CompanyId <= 100.
In the current stored procedure this comes in as Filter Fields: MachineId,CompanyId and Filter Values: <>109;<=100.
A user defined function will use the datatype of the corresponding filterfield to determine if the inputted filter parameter is correct (well it does more then that), if the filter value can not be applied a corresponding Error Message must be given, multiple error messages should be possible.
The resulting dataset is eventually used for a report in reporting services, since you need to say how many columns you need and their names for the report I came up with renaming the columns to Col1 to Col10 and for the headers I used Col1A to Col10A.
December 10, 2010 at 2:20 am
Resender (12/10/2010)
OK so the function I found (DelimitedSplit8K)returns me the delimited varchar as a table where each record is the next value
turns into
ID ColumnName
1 MachineId
2 RegisterId
3 UtcTime
4 Value
I can join this with the INFORMATION_SCHEMA.COLUMNS and I'll get the following:
CompanyId in table dbo.Machine, is NULL-able, of the datatype int
CreatedUtcTime in table dbo.Machine, is NULL-able, of the datatype datetime
MachineId in table dbo.Machine, is not NULL-able, of the datatype int
MachineSerialNumber in table dbo.Machine, is not NULL-able, of the datatype varchar with a maximum character length of 100.
Since the nature of what I want to achieve I want to execute this from the master systemdatabase, since this is
A)Always present on an SQL Server Installation
B)Contains all metadata
If I just join INFORMATION_SCHEMA.COLUMNS from the master database for columns in a table in the MachineRegistration database I will get zero results.
In order to get results I would need to join the INFORMATION_SCHEMA.COLUMNS from the database MachineRegistration.
Next up would be using that information to get the size of the dataset.
I could do the same for the filter fields and filtervalues I want to pass, let say I want all the MachineId <> 109 and CompanyId <= 100.
In the current stored procedure this comes in as Filter Fields: MachineId,CompanyId and Filter Values: <>109;<=100.
A user defined function will use the datatype of the corresponding filterfield to determine if the inputted filter parameter is correct (well it does more then that), if the filter value can not be applied a corresponding Error Message must be given, multiple error messages should be possible.
The resulting dataset is eventually used for a report in reporting services, since you need to say how many columns you need and their names for the report I came up with renaming the columns to Col1 to Col10 and for the headers I used Col1A to Col10A.
OK, so you want to run the procedure from master yet get column information from a database that is specified at runtime. So you just qualify the column view with the name of the database, which is what you appear to have done in the code you posted yesterday. In dynamic SQL, a very simplified version would look something like this:
SET @SQL = 'SELECT * FROM ' + @DB + '.INFORMATION_SCHEMA.COLUMNS'
EXEC (@SQL)
I wouldn't actually recommend using EXEC - you should use sp_executesql for reasons you'll understand if you have read the dynamic SQL article.
John
December 10, 2010 at 2:48 am
Yep, that's right and with the DelimitedSplit8K function join them the INFORMATION_SCHEMA.COLUMNS I will get the datatype and maximum allowed character length.
Now this returns me a table with my columnname datatype and maximum allowed character length, now I need to calculate the estimated size of my dataset and turn it so I will get the following select
select 'MachineId' As Col1A, MachineId As Col1, 'RegisterId' As Col2A, RegisterId As Col2 ...
So that I can use the storre filters so that the input MachineId,CompanyId and Filter Values: <>109;<=100 would result in the following results
MachineId int null <>109
CompanyId int null <=100
Now I need to also check if the inputed filter values are correct, my first through is to loop through the table and pass each record to the udf. But this can presumably be avoided somehow.
December 10, 2010 at 3:03 am
This is where dynamic SQL starts to get ugly and is one of the reasons why you should try to avoid it. I think you would have to loop through each pairing of filter field and filter value. You could then do a check to verify that the data supplied is of the correct type and/or that it is within a permitted range.
However, I would recommend that you have split your code up so that you have separate stored procedures for each column or set of columns and each equality/inequality operator. You may think that managing so many different procs is an administrative nightmare, but it my opinion it is preferable to having lines and lines of inelegant and inefficient dynamic code. You will also be aware from the article of the benefits in terms of security and (possible) performance improvements that are to be gained by dispensing with dynamic SQL.
John
December 10, 2010 at 3:45 am
For the filter I already did this, in the following manner
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)
I'll give some basic explanantions on the code and how what you told me will be a significant improvement.
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
Is the way the stored procedure currently determines the datatype of a filter field, by using the delimit function combined with the INFORMATION_SCHEMA.COLUMNS join I will have
the following (note that atm I'm working on a test database)(This code delimits the filter values and filterfields inputs)
use Test
declare @ff nvarchar(max)
declare @fv nvarchar(max)
set @ff = 'MachineId,CompanyId'
set @fv = '<>109;<=100;;C'
select ds1.Item, ds2.Item from [Test].[dbo].[DelimitedSplit8K] (@ff,',') ds1
full outer join [Test].[dbo].[DelimitedSplit8K] (@fv,';') ds2 on ds1.ItemNumber = ds2.ItemNumber
This will give me a table with following records
MachineId<>109
CompanyId<=100
NULL
NULLC
So when I loop through the table I can immediately ignore those that have NULL as filterfield (Well ignore in the sense of passing the appropriate error message)
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)
My udf dbo.DynamicSQlFilter2 requires 3 parameters: datatype, columnname and filterfields.
It returns me a table containing a string for the filter and if needed an error message.
So now with the delimit function combined with the INFORMATION_SCHEMA.COLUMNS will make a whole lot of code obsolete:-).
EDIT
Well it won't be as easy as I hoped but I hope to put up some code soon.
December 10, 2010 at 4:00 am
I'm glad you've made some progress. Your SET @par2 and SET @filter statements will fail if the respective temp tables contain more than one row, however.
John
December 10, 2010 at 5:52 am
Currently I solved that by truncating the temp tables after I did the set, also most of the time I was sure this would only give me one result
For the filtering I now have the following test case (on a database Test)
CREATE TABLE [dbo].[Machine](
[MachineId] [int] NULL,
[CompanyId] [int] NULL,
[UtcTime] [datetime] NULL,
[Value] [int] NULL,
[Description1] [nvarchar](20) NULL
) ON [PRIMARY]
GO
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
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply