January 11, 2018 at 11:10 am
Hi All,
I have query which has 20 columns along with alias names for the column. The plan is to have two parameters one is for where clause. This where clause will have something like ‘where columnname1 like @column1 and columnname2 like @column2’ and one more parameter for parameter values which will be array of value (value1, value2). So I need to map the column names with query column names and then map values to column names. How to achieve this ? Is there any better way to do this ?
Here the column selection itself is dynamic as well as values need to be mapped correctly to the right column for filtering the data.
Thanks in advance!!
January 11, 2018 at 11:36 am
alladiz - Thursday, January 11, 2018 11:10 AMHi All,
I have query which has 20 columns along with alias names for the column. The plan is to have two parameters one is for where clause. This where clause will have something like ‘where columnname1 like @column1 and columnname2 like @column2’ and one more parameter for parameter values which will be array of value (value1, value2). So I need to map the column names with query column names and then map values to column names. How to achieve this ? Is there any better way to do this ?Here the column selection itself is dynamic as well as values need to be mapped correctly to the right column for filtering the data.
Thanks in advance!!
Looks like dynamic SQL. Other than that, not much more I can say since you really haven't given us much to work with in your post.
January 11, 2018 at 11:43 am
always a good idea to be familiar with the "catch all queries" thing talked about here:
https://www.sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
January 12, 2018 at 1:26 am
Thanks so much for the taking time to go through and reply. I should have given a proper example for better explanation of the scenario. I am sorry for that. I have mocked up some sample data for this.
create table #temp (
col1 varchar(100),
col2 varchar(100),
col3 varchar(100),
col4 varchar(100),
col5 varchar(100),
col6 varchar(100)
)
INSERT INTO #temp
SELECT 'iphone', 'x','black','64gb','smartphone','apple'
union
SELECT 'iphone', 'x','black','256gb','smartphone','apple'
union
SELECT 'iphone', 'x','white','64gb','smartphone','apple'
union
SELECT 'iphone', 'x','white','256gb','smartphone','apple'
union
SELECT 'iphone', '8Plus','black','64gb','smartphone','apple'
union
SELECT 'iphone', '8Plus','black','256gb','smartphone','apple'
union
SELECT 'Galaxy', 'S8','black','64gb','smartphone','Samsung'
union
SELECT 'Galaxy', 'S8','black','128gb','smartphone','Samsung'
union
SELECT 'Galaxy', 'S8Plus','black','64gb','smartphone','Samsung'
union
SELECT 'Galaxy', 'S8Plus','black','128gb','smartphone','Samsung'
select col1 AS Model,
col2 AS Variant,
Col3 AS Color,
Col4 AS Capactiy,
Col5 AS PhoneType,
Col6 AS Company
from #temp
drop table #temp
Based on the above data, the filter could be based on Col1 which is Model or Col6 which is companyname or both Model and companyname or it could be any column from the above query. The reason I have given the column names as "col1" because the where clause parameter value will be something like "where Model like @Model and Company like @company". So I need map based on Alias names and the parameter which passes value will be like "Galaxy,Samsung".
Is there any better way of getting this accomplished ?
Thanks in advance!!
January 12, 2018 at 1:49 am
Withthe data you've provided though, how do we know which col is model, which col is Company? Aliases' can't be referenced in a WHERE clause. Is it always:
col1 = Model,
col2 = Variant,
Col3 = Color,
Col4 = Capactiy,
Col5 = PhoneType,
Col6 = Company
If so, I have to ask, but why are you not giving your columns proper names? This would back it very hard for someone else to work with if they don't know the structure of your data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2018 at 2:13 am
Thanks for the reply Thom. The code that I have pasted above use the Alias names. The "where" and "parameter" parameters come from the application. The application is aware of Alias names not the actual column names using the data reader. That's the reason I had to provide example in that manner. The actual column name in DB is different and the application is using Alias Names for better readability.
January 12, 2018 at 2:28 am
alladiz - Friday, January 12, 2018 2:13 AMThanks for the reply Thom. The code that I have pasted above use the Alias names. The "where" and "parameter" parameters come from the application. The application is aware of Alias names not the actual column names using the data reader. That's the reason I had to provide example in that manner. The actual column name in DB is different and the application is using Alias Names for better readability.
Are you saying that col1 may not actually be called, col1? As I said, you can't reference an Alias in the WHERE clause, thus, things like the following would fail:
DECLARE @Company varchar(100);
SET @Company = 'Samsung';
SELECT col6 AS Company
FROM YourTable
WHERE Company = @Company;
Somewhere, the application/SQL Server (or we (not you)) need to know how we can obtain the alias of a column without knowledge of the data or your application. Even if that's some kind of table.
For example, if I were to give you the following data;SELECT 8 AS Col1
'ABCD01' AS Col2,
'MR' AS Col3,
'John Smith' AS Col4,
'Rev' AS Col5,
'Swindon' AS Col6;
Could you tell me, just using that information, what columns are the following: Title, Name, Reference, Branch, Type and Agent? The answer is "No"; you have no context for what those columns represent.
Yyou could take a guess; perhaps "MR" is the Title, it's looks like "Mr", just all in caps. "Swindon" might be the branch, it's a town, so that makes sense. "John Smith" is obviously a name. 8 is perhaps Type, which leaves Reference and Agent. Let's guess Reference is "ABCD01" and "Rev" is agent.
Well, some of those answers are right, but not all of them.
We'd need a way of being able to do so. Thus, say we had a lookup table:CREATE TABLE ColLookup (TableName nvarchar(100), ColumnName nvarchar(100), ColumnAlias nvarchar(100));
INSERT INTO ColLookup
VALUES (N'Customer', N'Col1', N'Branch'),
(N'Customer', N'Col2', N'Reference'),
(N'Customer', N'Col3', N'Type'),
(N'Customer', N'Col4', N'Name'),
(N'Customer', N'Col5', N'Title'),
(N'Customer', N'Col6', N'Agent');
Now we know which column has which alias, and we can build off that.
Without some kind of lookup/key we don't know what a model is in respect of your sample table; as there is no column Model.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2018 at 2:46 am
Thanks Thom for the detailed explanation. We definitely need to build look up table to map column names to alias names. We need to parse through the where parameter and get the columns and build where clause using those details. I believe SQL Server is not strong at string parsing. We also need to be aware of the ordinal position of the "parameter" values to map values to right column for filtering. That requires parsing as well. So it might cause performance issue. Please correct me if i am wrong on this
The main requirement is to build filtering dynamically where column used for filtering is dynamic as well. Is there any better way to accomplish this ?
January 12, 2018 at 3:51 am
alladiz - Friday, January 12, 2018 2:46 AMThanks Thom for the detailed explanation. We definitely need to build look up table to map column names to alias names. We need to parse through the where parameter and get the columns and build where clause using those details. I believe SQL Server is not strong at string parsing. We also need to be aware of the ordinal position of the "parameter" values to map values to right column for filtering. That requires parsing as well. So it might cause performance issue. Please correct me if i am wrong on thisThe main requirement is to build filtering dynamically where column used for filtering is dynamic as well. Is there any better way to accomplish this ?
We can certainly do something, using a Lookup Table, but we kind of need to know how you're plannning to interact with your data. re you saying you want to pass parameters passing an entire WHERE clause? Such as "WHERE Model = 'Samsung' AND Capacity = '128gb'"? This is a VERY BAD idea. This would easily be open to SQL injection.
Personally, I'd go with using Delimited Strings, or a User-Defined Table Type Parameter. Are these options?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2018 at 3:58 am
Thanks Thom for the reply. I totally agree with you on SQL injection. Delimited strings will be a batter option.
January 12, 2018 at 4:19 am
OK, I've done a solution for you using both a Delimited String, and a User-Defined Table Type. Note that the Delimited String solution user's Jeff Moden's DelimitedSplit8K, which you'll need on your local instance to be able to use. The UDTT does not require that. Both solutions require a Column lookup Table (ColLookup).
There is no error handling in this either, which is something you'll want to consider; for example, generating an error if the Delimited Strings don't have an equal number of delimiters.
I haven't annotated any of this, so if you don't understand, ask. It's important you know what it's doing, I can't debug your application for you in the future.; that's your job, not mine. Apologies for the alignment issues, that's SSC's fault.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2018 at 4:27 am
Awesome!! Thanks so much Thom for the solution provided. I will take a look at it and let you know if I have any questions.
Thanks again!!
January 12, 2018 at 7:46 am
Thom A - Friday, January 12, 2018 4:19 AMOK, I've done a solution for you using both a Delimited String, and a User-Defined Table Type. Note that the Delimited String solution user's Jeff Moden's DelimitedSplit8K, which you'll need on your local instance to be able to use. The UDTT does not require that. Both solutions require a Column lookup Table (ColLookup).There is no error handling in this either, which is something you'll want to consider; for example, generating an error if the Delimited Strings don't have an equal number of delimiters.
I haven't annotated any of this, so if you don't understand, ask. It's important you know what it's doing, I can't debug your application for you in the future.; that's your job, not mine. Apologies for the alignment issues, that's SSC's fault.
USE Sandbox;GOCREATE TABLE Table1(Col1 int,Col2 varchar(6),Col3 char(2),Col4 varchar(50),Col5 varchar(3),Col6 varchar(50));GOINSERT INTO Table1SELECT 8 AS Col1,'ABCD01' AS Col2,'MR' AS Col3,'John Smith' AS Col4,'Rev' AS Col5,'Swindon' AS Col6;GOCREATE TABLE ColLookup (TableName nvarchar(100), ColumnName nvarchar(100), ColumnAlias nvarchar(100));INSERT INTO ColLookupVALUES (N'Table1', N'Col1', N'Branch'),(N'Table1', N'Col2', N'Reference'),(N'Table1', N'Col3', N'Type'),(N'Table1', N'Col4', N'Name'),(N'Table1', N'Col5', N'Title'),(N'Table1', N'Col6', N'Agent');GO--Using a Delimited StringCREATE PROC GetData_DS @Table nvarchar(100), @Columns nvarchar(max), @Values nvarchar(max) ASCREATE TABLE #Lookups (ColmnName nvarchar(100), ColumnAlias nvarchar(100), ColumnValue nvarchar(100));INSERT INTO #LookupsSELECT CL.ColumnName,C.Item AS ColumnAlias,V.Item AS ColumnValueFROM dbo.DelimitedSplit8K(@Columns, ',') CCROSS APPLY (SELECT *FROM dbo.DelimitedSplit8K(@Values, ',') DSWHERE DS.ItemNumber = C.ItemNumber) VJOIN ColLookup CL ON C.Item = CL.ColumnAlias;DECLARE @SQL nvarchar(MAX);SELECT @SQL = N'SELECT *FROM ' + QUOTENAME([name]) + N'WHERE 'FROM sys.tablesWHERE [name] = @Table;SELECT @SQL = @SQL + STUFF((SELECT NCHAR(10) + N' AND ' + QUOTENAME(c.[name]) + N' = ''' + REPLACE(L.ColumnValue,N'''',N'''''') + N''''FROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idJOIN #Lookups L ON c.[name] = L.ColmnNameWHERE t.[name] = @TableFOR XML PATH (N'')),1,11,N'') + N';';PRINT @SQL;EXEC sp_executesql @SQL;DROP TABLE #Lookups;GOEXEC GetData_DS N'Table1', N'Branch,Reference', N'8,ABCD01';GO--User-Defined Table Type SolutionCREATE TYPE DataCols AS TABLE (ColumnAlias nvarchar(100), ColumnValue nvarchar(100));GOCREATE PROC GetData_UDTT @Table nvarchar(100), @Data DataCols READONLY ASDECLARE @SQL nvarchar(max);SELECT @SQL = N'SELECT *FROM ' + QUOTENAME([name]) + N'WHERE 'FROM sys.tablesWHERE [name] = @Table;SELECT @SQL = @SQL + STUFF((SELECT NCHAR(10) + N' AND ' + QUOTENAME(c.[name]) + N' = ''' + REPLACE(D.ColumnValue,N'''',N'''''') + N''''FROM sys.columns cJOIN sys.tables t ON c.object_id = t.object_idJOIN ColLookup CL ON c.[name] = CL.ColumnNameAND t.[name] = CL.TableNameJOIN @Data D ON CL.ColumnAlias = D.ColumnAliasWHERE t.[name] = @TableFOR XML PATH (N'')),1,11,N'') + N';';PRINT @SQL;EXEC sp_executesql @SQL;GODECLARE @DataValues DataCols;INSERT INTO @DataValuesVALUES (N'Branch', N'8'),(N'Reference', N'ABCD01');EXEC GetData_UDTT N'Table1', @DataValues;GODROP PROC GetData_UDTT;DROP TYPE DataCols;DROP PROC GetData_DS;DROP TABLE ColLookup;DROP Table Table1;GO
Only critique I have with the code is with the data type used in the lookup table and table type. These should use the same data type as the system tables which is sysname.
January 12, 2018 at 7:51 am
Lynn Pettis - Friday, January 12, 2018 7:46 AMOnly critique I have with the code is with the data type used in the lookup table and table type. These should use the same data type as the system tables which is sysname.
Good point there Lynn, not sure why I used nvarchar...
Edit: Updated to use sysname where appropriate.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply