EXTREMELY dynamic query 8

[gbaughma]

So I'm looking for a push in the right direction, short of building a HUGE dynamic query with a TON of IF statements.

Here's the scenario.

I'm doing an "Ad-hoc" report, based off of a couple of tables. Joining the tables is no big deal. It's call record data.

I have an input form, which will consist of a bunch of checkboxes and text fields.
For Example:
(Checkbox) Name (Textbox)
(Checkbox) City (Textbox)

So, if someone puts a check in the checkbox, it needs to select/show that field. If someone types into the textbox, it needs to filter by that field.
It could be any combination.... for example, it could be a checkbox in the name (show the names) no checkbox in the city, but a city name in the City textbox, meaning "I want to select Name where city = (textbox value)"

Needless to say, this will have a lot more checkboxes and fields than just two.

So, has someone done this? Like I said, I'd rather use some logic besides
If @chkName = true Then
@sql = @sql + ', Name'
End

If @txtName != '' Then
@sql = 'AND Name = ''' + @txtName + '''
End
... sort of stuff. I'm sure I'll end up having to concatenate a long SQL query and execing it... but is there a better way than brute force with if statements?


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[SgtJarrow]

I'm always of the opinion that the UI should do the DISPLAY portion and the SQL should do the DATA portion. If I follow your request, how about something like this?

Create a single sproc to accept the input parameters and to return the data.

DECLARE @t1Check BIT;
DECLARE @t2Check BIT;
DECLARE @t1ColumnFilter VARCHAR(50);
DECLARE @t2ColumnFilter VARCHAR(50);

SELECT
    CASE WHEN @t1Check = 1 THEN t1.Column ELSE 'Hide' END 't1Column',
    CASE WHEN @t2Check = 1 THEN t2.Column ELSE 'Hide' END 't1Column'
FROM TABLE1 t1
INNER JOIN TABLE2 t2
    ON t1.Column = t2.Column
WHERE (t1.Column = @t1ColumnFilter OR @t1ColumnFilter IS NULL)
    AND (t2.Column = @t2ColumnFilter OR @t2ColumnFilter IS NULL)

The checkbox and textbox values for each possible are evaluated in this case. If there checkbox was checked, the value in the column will be returned. If the checkbox was not checked, every row will return 'Hide' (no quotes). If the textbox was empty, query is not filtered and if a value was supplied, it is filtered for that value only. No dynamic SQL needed in this case!

Then your display (SQL Server Reporting Services Report, DataGrid(View), etc) can evaluate the columns and set the Visible attribute based on the data in the column - specifically if the value returned is "hide' then set the visible attribute for the column to false.

See my example code below:

DECLARE @Data TABLE
(
	Column1		VARCHAR(50),
	Column2		VARCHAR(50),
	Column3		VARCHAR(50)
);

INSERT INTO @Data (Column1, Column2, Column3) VALUES ('A', 'B', 'C');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('D', 'E', 'F');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('R', 'S', 'T');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('X', 'Y', 'Z');

DECLARE @C1Check BIT;
DECLARE @C1Text VARCHAR(50);
DECLARE @C2Check BIT;
DECLARE @C2Text VARCHAR(50);
DECLARE @C3Check BIT;
DECLARE @C3Text VARCHAR(50);

SET @C1Check = 1;
SET @C2Check = 1;
SET @C3Check = 0;

SET @C1Text = NULL;
SET @C2Text = NULL;
SET @C3Text = 'C';

SELECT
	CASE WHEN @C1Check = 1 THEN d.Column1 ELSE 'Hide' END 'Column1',
	CASE WHEN @C2Check = 1 THEN d.Column2 ELSE 'Hide' END 'Column2',
	CASE WHEN @C3Check = 1 THEN d.Column3 ELSE 'Hide' END 'Column3'
FROM @Data d
WHERE (d.Column1 = @C1Text OR @C1Text IS NULL)
	AND (d.Column2 = @C2Text OR @C2Text IS NULL)
	AND (d.Column3 = @C3Text OR @C3Text IS NULL);

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[gbaughma]

Interesting approach...
I do something similar with my select statements like:

SELECT * FROM table WHERE (Agency=@Agency OR @Agency='ALL')

... where "ALL" is a default in a dropdown.

To return just the columns, looking at this, I could do:

SELECT
CASE WHEN @C1Check = 1 then d.Column1 END
FROM Data d

... and that would return the column if it's checked?

My goal is to take the resultant recordset and just bind it to a dataview in a report. So filtering out the columns that said "hide" would be an additional bit of programming.

Let me work with this concept and play with it. Thank you!



Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[SgtJarrow]

I believe the column is returned either way. If I remove the ELSE part of the test data and run it, the column returns NULL for the "hidden" column. So I think you still might need to do some front end programming... Maybe at your datalayer code you can remove the "hidden" columns and then pass the dataset to the dataview?

Hopefully you find a solution that works for you..

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[Olaf Doschke]

SELECT CASE WHEN @C1Check = 1 then d.Column1 END

Even without an ELSE this sill still results in a unnamed column with NULL, in case @ClCheck is not 1. I'd put in another thought and say in general ad hoc queries aren't that bad, that means your additional thought is quite valid to put together the field list. If your UI to check the wanted columns is just allowing selection or no selection of columns you also don't have to be afraid of users hacking the database and injecting something into the query.

You just don't puzzle the query in T-SQL, but on the client side and finally execute the statement, ideally parameterized in terms of the WHERE columnX = @somevalue clauses to prevent injections of query language parts instead of just the values to filter for.

How that is best achieved depends on your client side language.

Just to demonstrate the query itself can be put together in T-SQL, too, use @SgtJarrowes code for definintg sample data and then compose @sql:

DECLARE @Data TABLE
(
	Column1		VARCHAR(50),
	Column2		VARCHAR(50),
	Column3		VARCHAR(50)
);

Declare @sql as NVarchar(MAX);
Declare @comma as Char(2);
Declare @and as Char(4);

INSERT INTO @Data (Column1, Column2, Column3) VALUES ('A', 'B', 'C');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('D', 'E', 'F');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('R', 'S', 'T');
INSERT INTO @Data (Column1, Column2, Column3) VALUES ('X', 'Y', 'Z');

DECLARE @C1Check BIT;
DECLARE @C1Text VARCHAR(50);
DECLARE @C2Check BIT;
DECLARE @C2Text VARCHAR(50);
DECLARE @C3Check BIT;
DECLARE @C3Text VARCHAR(50);

SET @C1Check = 1;
SET @C2Check = 1;
SET @C3Check = 0;

SET @C1Text = ';
SET @C2Text = ';
SET @C3Text = 'C';


Set @comma = ';
Set @and = ';

Set @sql='SELECT ';
If @C1Check = 1  
Begin 
   SET @sql = @sql + 'd.Column1';
   Set @comma = ', ';
End

If @C2Check = 1
Begin 
   SET @sql = @sql + @comma+'d.Column2';
   Set @comma = ', ';
End

If @C3Check = 1
Begin 
   SET @sql = @sql + @comma+'d.Column3';
End

Set @sql = @sql + ' FROM @Data d WHERE '

If @C1Text != ' 
BEGIN 
   SET @sql = @sql + 'd.Column1 = @C1Text';
   Set @and = 'AND ';
END

If @C2Text != ' 
BEGIN 
   SET @sql = @sql + @and +'d.Column2 = @C2Text';
   Set @and = 'AND ';
END

If @C3Text != ' 
BEGIN 
   SET @sql = @sql + @and +'d.Column3 = @C3Text';
END

PRINT @sql;

The generated query would work nicely as is, but this fails on EXECUTE sp_executesql @sql, because the declared variables are not available in the sp_executesql procedure, things like the @Data table and the @C1... etc variables are limited to the current scope, but C# or other .NET lanaguages allow to build up SQLCommands and add parameters to them.

Also client side languages allow prettier structures to be used to put together such a dynamic query, foreach loops on arrays, collections or other enumarable collections of the columns and the user input about them.

Nevertheless I'm with SgtJarrow in the aspect he shows: You can put together a query that can react to variables being set or NULL. It's just a small overhead in comparison to the idealized specific query you can put together with code and in simpler cases this can work out fine without much code to put together a dynamic query.

Bye, Olaf.

 

[gbaughma]

Yeah... I stumbled on the same thing myself... I get NULL columns....

Logically, if I do a:
CASE WHEN @chkName = 1 then t1.Name END

... it would only return the name column when the condition is true.

However, in practice, it will return a NULL column for that location.

Now, I could understand this if I were to do a:
CASE WHEN @chkName = 1 then t1.Name END [Name]

... because [Name] would be a defined column name outside of the conditions.

Additionally, it does not like:
CASE WHEN @chkName = 1 then t1.Name AS [Name] END

... which, logically, should work as well.

Building a long string query and executing it is fine and dandy... keeping in mind I may very well end up with a couple of DOZEN check boxes and text boxes before this is all over.... and going against a table with millions of records.

If that's the route I have to take, I can work with that. I was just hoping for something more straight forward with 1/4 of the total lines of logic.


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

Seems to be a posting problem: Many double ' (for empty strings) only came over as single '.

Bye, Olaf.

 

[Olaf Doschke]

> something more straight forward with 1/4 of the total lines of logic.
Well, that depends on your client side language, because they are used to build the ad hoc query.

Bye, Olaf.

 

[gbaughma]

Olaf:

I didn't notice the syntax until you mentioned. Read it just fine... thank you.


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[SgtJarrow]

Olaf's comments are spot on as well. His code example expands on mine and just shows there are more than one way something like this can be accomplished.

It's not always the end result: the journey is important too.

As long as you (read us - meaning anyone reading this post) are learning something that helps you find the solution to your problem, then we have done our job. While clean, easily maintainable code is a great end result, as long as you have something that works, meets your requirements and does no jeopardize your system/data then that is the correct solution.

I don't know how many times I have gone back to some old code and said: What was I thinking?!? That can be done so much easier and better like this... But that old clunky code did that job at the time.

The day we stop learning, well...that's just not a good day.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[DaveInIowa]

Would a pattern like this work?

DECLARE -- Constants
	@False	BIT	=	0,
	@True	BIT	=	1

IF (OBJECT_ID('tempdb..#MyTable') IS NOT NULL) DROP TABLE #MyTable

SELECT *
  INTO #MyTable
  FROM MyTable
 WHERE (@Column1Filter IS NULL OR Column1 LIKE @Column1Filter)
    OR (@Column2Filter IS NULL OR Column2 LIKE @Column2Filter)
    OR (@ColumnNFilter IS NULL OR ColumnN LIKE @ColumnNFilter)

IF (@IncludeColumn1 = @False) ALTER TABLE #MyTable DROP COLUMN Column1
IF (@IncludeColumn2 = @False) ALTER TABLE #MyTable DROP COLUMN Column2
IF (@IncludeColumnN = @False) ALTER TABLE #MyTable DROP COLUMN ColumnN

SELECT *
  FROM #MyTable

 

[SgtJarrow]

Beautiful solution! Clean, concise, self documenting... I like that and will add that to my bucket 'o tricks. Thanks Dave!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
Senior Database Developer

 

[DaveInIowa]

Just noticed an error; posted too quickly. Each line of the WHERE clause should be AND instead of OR. And '(@IncludeColumnN = @False)' would probably work better with '(@IncludeColumnN <> @True)' to remove the column if its include flag is NULL. Also you wouldn't want to do a 'SELECT *' but just include all of your base columns.
Oh, and using LIKE instead of = would allow your users to perform a generic search if they desired.

 

[DaveInIowa]

Just to correct the bad advice I gave in the prior post, using '(@IncludeColumnN <> @True)' won't work any better if @IncludeColumnN is NULL. Instead use:

IF (COALESCE(@IncludeColumnN, @False) = @False) ALTER TABLE #MyTable DROP COLUMN ColumnN
-- Or
IF (COALESCE(@IncludeColumnN, @False) <> @True) ALTER TABLE #MyTable DROP COLUMN ColumnN

 

[gmmastros]

Dave's solution will be fine as long as there's not a lot of data in the table. Basically, don't expect this to perform as well as dynamic SQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Yeah... there's a lot of data... 4.8M records on my test data....

I'm building dynamic SQL. But all of these solutions have been educational and I thank you all!

And good to see you again George!


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

I second George, first fetching all columns, then dropping some you a) load too much data and b) modify the tble schema several times. In some databases that would mean building up the table multiple times. Within SQL Server Ithink pages are not rewritten, only the dropped column is invalidated, stil you build up more than needed. This is just one step ahead of querying all data to the client and let the report engine not print some columns and skip records not fitting, you do the data dropping server side and only pull the final temp table over the network, but you hog the server side.

Besides, when you start thinking of the UI needed for the user to pick columns and enter filtering values, you already need meta data about the table(s), eg a list of column names, which means the frontend will have the lists of meta data (names) needed to do the dynamic sql composition. It doesn't need to be done the way I showed. Eg in C# you easily create the comma separeted list of column names in a foreach loop over the ticked columns, which would be in an array, list or collection, see

http://www.dotnetperls.com/convert-list-string.

Bye, Olaf.

 

[DaveInIowa]

From a maintenance standpoint for a dynamic query, would the following code sample be helpful?

SET NOCOUNT ON

DECLARE
	@False BIT = 0,
	@True BIT = 1

DECLARE
	@IncludeColumn1 BIT = 1,
	@IncludeColumn2 BIT = 0,
	@IncludeColumn3 BIT = 1,
	@IncludeColumn4 BIT = 0,
	@IncludeColumn5 BIT = 1,
	@IncludeColumn6 BIT = 0,
	@Column1Filter VARCHAR(128) = '',
	@Column2Filter VARCHAR(128) = 'This',
	@Column3Filter VARCHAR(128) = NULL,
	@Column4Filter VARCHAR(128) = 'That'

DECLARE @Code CHAR
DECLARE @Value VARCHAR(128)
DECLARE @Values TABLE (Code CHAR, Value VARCHAR(128))

-- Define columns
IF (@IncludeColumn1 = @True) INSERT INTO @Values VALUES ('S', 'Column1')
IF (@IncludeColumn2 = @True) INSERT INTO @Values VALUES ('S', 'Column2')
IF (@IncludeColumn3 = @True) INSERT INTO @Values VALUES ('S', 'Column3')
IF (@IncludeColumn4 = @True) INSERT INTO @Values VALUES ('S', 'Column4')
IF (@IncludeColumn5 = @True) INSERT INTO @Values VALUES ('S', 'Column5')
IF (@IncludeColumn6 = @True) INSERT INTO @Values VALUES ('S', 'Column6')

-- Define filters
IF (LEN(@Column1Filter) > 0) INSERT INTO @Values VALUES ('W', 'Column1 = ''' + @Column1Filter + '''')
IF (LEN(@Column2Filter) > 0) INSERT INTO @Values VALUES ('W', 'Column2 = ''' + @Column2Filter + '''')
IF (LEN(@Column3Filter) > 0) INSERT INTO @Values VALUES ('W', 'Column3 = ''' + @Column3Filter + '''')
IF (LEN(@Column4Filter) > 0) INSERT INTO @Values VALUES ('W', 'Column4 = ''' + @Column4Filter + '''')

-- Initialize SELECT and WHERE clauses
DECLARE @SelectClause VARCHAR(512) = 'SELECT '
DECLARE @FromClause VARCHAR(512) = 'FROM MyTable'
DECLARE @WhereClause VARCHAR(512) = 'WHERE '
DECLARE @SelectDelimiter VARCHAR(2) = ''
DECLARE @WhereDelimiter VARCHAR(5) = ''

-- Build SELECT and WHERE clauses
WHILE (EXISTS (SELECT 1 FROM @Values)) BEGIN
	SELECT TOP 1 @Code = Code, @Value = Value FROM @Values

	IF (@Code = 'S') BEGIN
		SET @SelectClause += @SelectDelimiter + @Value
		SET @SelectDelimiter = ', '
	END
	ELSE IF (@Code = 'W') BEGIN
		SET @WhereClause += @WhereDelimiter + @Value
		SET @WhereDelimiter = ' AND '
	END

	DELETE @Values WHERE Code = @Code AND Value = @Value
END

DECLARE @Query VARCHAR(512) = @SelectClause + ' ' + @FromClause + ' ' + @WhereClause
PRINT @Query

 

[Olaf Doschke]

That's better, but the SQL Server will never be the UI for selecting the columns and setting the filters. It's fine we also have "collections" by using tables and can loop them, but I still vote for creating the queries in the frontend.

Bye, Olaf.