CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
user153923
user153923

Reputation:

Building SQL SELECT Columns by Option Parameters

I've been asked to create a stored procedure that will accept a number of options (eight Boolean values at the moment) to indicate what columns are included.

I could create a separate SELECT statement for each case, but 8² (eight square) = 64 separate select statements. Add 1 more parameter and I now have 9² = 81 separate select statements. That adds up quickly.

I am using Microsoft's SQL Server Management Studio (SSMS) because I am not a strong SQL writer and it tells me when I have done something wrong.

If I build a dynamic SQL statement, I lose the code checking ability of SSMS.

Q: Is there a way to specify the SELECT statement columns using parameter values?

Here is a very crude example that I have tried piecing together using SQL Fiddle:

http://sqlfiddle.com/#!3/5ef0f/1

Schema:

create table Invoice 
(
    Id int IDENTITY(1,1) NOT NULL,
    Name varchar(20) NOT NULL,
    Retail decimal(9, 4) NOT NULL,
    Quoted decimal(9, 4) NOT NULL,
    TaxCodeId int NULL
);

create table TaxCode 
(
    Id int IDENTITY(1,1) NOT NULL,
    Name varchar(20) NOT NULL,
    Rate decimal(9, 4) NOT NULL
);

Stored procedure:

CREATE PROCEDURE TestProcedure
    (@showPrice bit, @showTaxRate bit) 
AS BEGIN
    SET NOCOUNT ON;

    SELECT 
        Invoice.Name,
        CASE WHEN @showPrice = 1 THEN Invoice.Retail, Invoice.Quoted, 
        END
        CASE WHEN @ShowTaxRate = 1 THEN TaxCode.Rate 
        END
    FROM 
        Invoice
    LEFT JOIN 
        TaxCode on Invoice.TaxCodeId = TaxCode.Id;
END;

Upvotes: 2

Views: 58

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

One way is to use Dynamic-SQL (concatenate column list or exclude from column list)

DECLARE @showPrice   BIT = 1
       ,@showTaxRate BIT = 0;

DECLARE @sql NVARCHAR(MAX) = 
N'SELECT i.Name,i.Retail,i.Quoted,tc.Rate 
  FROM Invoice i
  LEFT JOIN TaxCode tc
    ON i.TaxCodeId=tc.Id;';

IF @showPrice = 0
  SET @sql = REPLACE(@sql, ',i.Retail,i.Quoted', '');

IF @showTaxRate = 0
  SET @sql = REPLACE(@sql, ',tc.Rate', '');

EXEC dbo.sp_executesql @sql;

LiveDemo

Second ugly way is to use temp table and DROP column:

DECLARE @showPrice   BIT = 1
       ,@showTaxRate BIT = 0;

SELECT i.Name,i.Retail,i.Quoted,tc.Rate 
INTO #temp
FROM Invoice i
LEFT JOIN TaxCode tc
  ON i.TaxCodeId=tc.Id;

IF @showPrice = 0
   ALTER TABLE #temp DROP COLUMN Retail, Quoted;   

IF @showTaxRate = 0   
   ALTER TABLE #temp DROP COLUMN Rate;   
   -- if you use it within SP you need to wrap it with `EXEC`
   -- EXEC('ALTER TABLE #temp DROP COLUMN Rate;');  

SELECT * FROM #temp;

LiveDemo2

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

You have hit on the two options and the drawbacks of each.

You can either use:

  1. Dynamic SQL, or
  2. IF logic that decides which SELECT (of 64) to execute.

There are no other ways.

If you are concerned about losing SSMS' code-checking ability, you can employ PRINT statements during development to see what your dynamic query looks like, and copy and paste that query into a new SSMS query to see what suggestions SSMS might have.

Upvotes: 3

Related Questions