December 21, 2018 at 9:24 am
We can filter Rows with WHERE clauses and JOINS etc
But is there an easy way to filter Columns
I was wondering if some feature of T-SQL would simplify this situation
IF @ExtendedParam = 1
BEGIN
SELECT A, B, C, D, E, F
FROM MyTable
END
ELSE IF @ExtendedParam = 2
BEGIN
SELECT A,B,C
FROM MyTable
END
ELSE IF @ExtendedParam = 3
.....
I have tried variations on
SELECT A, B, C
,CASE WHEN @ExtendedParam = 1 THEN D
END
,CASE WHEN @ExtendedParam = 1 THEN E
END
,CASE WHEN @ExtendedParam = 1 THEN F
END
FROM @MyTable
This is no good to me because it outputs the columns I want to exclude from the results
I have not found a way of filtering on Columns without creating a separate SELECT statement for each variation of the filter
In the real query @ExtendedParam has (currently) 8 values - meaning I have 8 copies of the 'SELECT A, B, C' part of the query. And that is actually quite complex with 35 columns and various formatting functions and case statements of its own, so it would be really useful not to have to repeat it 8 times!
December 21, 2018 at 10:25 am
You might have to use dynamic SQL to achieve this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 21, 2018 at 11:53 am
Why would you want to return a different set of data? This type of output could not bind to external applications like SSRS/SSIS because the system would not be able to determine what columns would be returned.
Another option would be to return a column that identified what type of data is being returned - and then populate the extra column with the appropriate data.
SELECT A, B, C, ExtendedParam = @ExtendedParam
,ParamValue = CASE WHEN @ExtendedParam = 1 THEN D
WHEN @ExtendedParam = 2 THEN E
WHEN @ExtendedParam = 3 THEN F
END
FROM @MyTable
If there are other column values to be returned for different conditions:
SELECT A, B, C, ExtendedParam = @ExtendedParam
,ParamValue = CASE WHEN @ExtendedParam = 1 THEN D
WHEN @ExtendedParam = 2 THEN E
WHEN @ExtendedParam = 3 THEN F
END
,ParamValue1 = CASE WHEN @ExtendedParam = 1 THEN E END
,ParamValue2 = CASE WHEN @ExtendedParam = 1 THEN F END
FROM @MyTable
With this you would always get all columns - but then only those columns that should be populated based on the parameters would have values. Downstream systems can then evaluate the ExtendedParam value and choose what columns to display or ignore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2019 at 2:59 am
And thank-you for your suggestions.
I Decided the Dynamic SQL route fitted my problem best.
The results go to an Excel Report for the end user - not to any further processing
In the interest of separation of concerns I created a function for each variable set of columns
CREATE FUNCTION dbo.ufn_Param1 ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN 'D, E, F'
END
GO
CREATE FUNCTION dbo.ufn_Param2 ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN 'D'
END
GO
CREATE FUNCTION dbo.ufn_Param3 ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN 'D, F'
END
GO
GO
CREATE FUNCTION dbo.ufn_ColList (@Param INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @RTN NVARCHAR(MAX);
SELECT @RTN = CASE @Param
WHEN 1 THEN dbo.ufn_Param1()
WHEN 2 THEN dbo.ufn_Param2()
WHEN 3 THEN dbo.ufn_Param3()
END;
Then the final query can be simplified to
DROP TABLE IF EXISTS ##MyTable;
DECLARE @ExtendedParam INT;
CREATE TABLE ##MyTable ( A INT, B INT, C INT, D VARCHAR(10), E VARCHAR(10), F VARCHAR(10) );
INSERT INTO ##MyTable
SELECT 1, 2, 3, 'D', 'E', 'F'
January 2, 2019 at 4:29 am
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2019 at 10:44 am
tom 69406 - Friday, December 21, 2018 9:24 AM
Can you afford to buy a book on basic RDBMS? You have missed the part about what a table is. A table, by definition, must have a key and must be made up of a fixed known number of columns. Those columns have known data types. Then I want you to go back and get a book on basic software engineering. It's not a big fad it was decades ago, but it really is important to know if you're going to work in IT. It's based on two concepts that you can Google; coupling and cohesion. Coupling is how tightly the code modules in the system (regardless of language!) are tied together. Cohesion is how well a module does one and only one task, with one and only one entry point and one and only one exit point. Over the last 50 years. We have found that systems with loose coupling and high cohesion are easier to maintain and work better. You can Google all of this might want to spend some time reading the thousands and thousands of pages of literature and proof of the statement.
What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.
The way in SQL programmer would do this would be to write a single query that gives him all the information he needs and then he would "toss it over the relational wall" to the next layer of the tiered architecture. It would probably be a presentation layer, I'm guessing. Then, once it's over the wall, that layer will format and pretty-print it for display and probably take out some of the unneeded columns.
One of the good tests, when you're designing a module of code, is to ask "what would a rational name for this module be?" If the name for this module includes something like "Britney Spears, Lady Gaga, automobiles and squids" in it, then it's probably doing too much. It's worse in the Swiss Army knife 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
January 3, 2019 at 2:38 am
@joe
[What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.]
Wow I can see you are a purist programmer, who likes rules to be followed. However unlike the immutable laws of physics, the rules of programming are just the opinions of other people, and so are pragmatic, flexible, and can be adapted by circumstances. One person's design flaw is another person's practical solution.
And you appear to have overlooked the fact that I have simplified the code to show the essence of the problem I wanted help with
The output is essentially a set of "what if" illustrations for the customer. If my customer wants a report with the same first 30 columns and 8 additional column sets as optional variations on that report, I am pragmatic: I don't tell her she is breaking the rules on what a table is, and I don't repeat the code for the first 30 columns in 8 separate reports. She gets the reports she wants by the quickest most efficient methods available to me.
As for getting the presentation layer to do the work - its too slow - I only pass the data requested - I can't assume they want the other 7 variations. SQL Server is very good at this stuff, even if it can't cache or optimise dynamic queries - it still beats excessive network traffic and the presentation layer.
January 4, 2019 at 8:07 am
tom 69406 - Thursday, January 3, 2019 2:38 AM@joe
[What you are asking us to help you do is a design flaw called "flag coupling", which means that the way your module works is determined by an external source passing flags that are not known until runtime into it. This is considered the worst way to write code in any language and has been for about 40+ years.]Wow
I can see you are a purist programmer, who likes rules to be followed. However unlike the immutable laws of physics, the rules of programming are just the opinions of other people, and so are pragmatic, flexible, and can be adapted by circumstances. One person's design flaw is another person's practical solution.And you appear to have overlooked the fact that I have simplified the code to show the essence of the problem I wanted help with
The output is essentially a set of "what if" illustrations for the customer. If my customer wants a report with the same first 30 columns and 8 additional column sets as optional variations on that report, I am pragmatic: I don't tell her she is breaking the rules on what a table is, and I don't repeat the code for the first 30 columns in 8 separate reports. She gets the reports she wants by the quickest most efficient methods available to me.
As for getting the presentation layer to do the work - its too slow - I only pass the data requested - I can't assume they want the other 7 variations. SQL Server is very good at this stuff, even if it can't cache or optimise dynamic queries - it still beats excessive network traffic and the presentation layer.
Yep.... @joe is a real curmudgeon with no concern for practicality, or apparently, even the standards he glorifies. He violates them at his convenience and then castigates others for the slightest infraction against them. Best bet... ignore him. It's so rare that he actually has something useful to offer, that's it not worth spending the time even reading his posts. I had had enough of him a long time ago.... And I tired of his "history lessons" even longer ago.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply