Using a WHERE clause as a parameter

• frankb 74878

  Old Hand

  Points: 316

  More actions

  February 28, 2017 at 4:46 pm

  #401707

  We're working on an ad hoc reporting system which would allow certain users to write their own WHERE clauses to query and report.  We're using one view for each report and then the user generates the WHERE clause using an in-house application.  What we're after would be a query for a Dataset something similar to this:
  SELECT CUSTOMER_NUM, CUSTOMER_NAME  FROM SYSADM.CUSTOMERS WHERE :pmWhere
  where we pass a WHERE clause to the parameter that can be as simple as "REGION = 1" or as complex as "REGION = 1 and TYPE = 5 or (LOCATION IN (1,3,97) and STATE = 'OR')" as the value of the parameter to pass into the report.  As a test case, I've been using "REGION = 1", but I can't get the query to execute after entering "REGION = 1" as the parameter value. 
  
  Are we barking up the wrong tree here?

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  February 28, 2017 at 5:01 pm

  #1930861

  Are you connecting to Oracle?

  If you are always going to filter a column, you could use a parameterized stored procedure...
  CREATE PROC GetMyData
     @param1 VARCHAR(20)
  AS
  SELECT {column list}
  FROM MyTable
  WHERE column1 = @param1

  Or you can add filters in your report to do it.  The difference is that having the filters inside the report will bring more data across the network and discard it locally instead of filtering before returning the data to SSRS.

• heb1014

  Hall of Fame

  Points: 3790

  More actions

  March 1, 2017 at 6:25 am

  #1930946

  Hi,

  What are you doing to avoid SQL Injection?

• Thom A

  SSC Guru

  Points: 99130

  More actions

  March 1, 2017 at 7:31 am

  #1930960

  Personally, I would go with a Stored Procedure and parametrise it. One, problem, however, is that you do have several options, and ideally, you want to use an IF statement to run the relevant query. This means that your SP will be quite bloated, but with a huge dataset, much more effecient.

  I've posted both solutions here, as I'm sure if I only supplied the first, several members of this community will hang me 😉 Yes, the latter solution (with Ifs), has a lot going on, but is the "better" solution. It all depends on what your needs are. If you have a lot more parameters, and not worried about speed, the first option might be "better" for you, but I would do some stress testing first.

  --Using Parameter testing and NULL Statements
CREATE PROC SYSADM.SearchCustomers_NoIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NOT NULL AND @State IS NOT NULL BEGIN --So that at least one parameter is passed

    SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE (C.REGION = @Region OR @Region IS NULL)
  AND (C.[TYPE] = @Type OR @Type IS NULL)
  AND (C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,',')) OR @Location IS NULL)
  AND (C.[STATE] = @State OR @State IS NULL)
  OPTION (RECOMPILE); --As the Query plan could be awful
END

  GO

  --Lots of IF statements
CREATE PROC SYSADM.SearchCustomers_WithIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region;
END
ELSE
IF @Type IS NOT NULL AND @Region IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type;
END
ELSE
IF @Location IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @State IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[TYPE] = @Type;
END
ELSE
IF @Region IS NOT NULL AND @Location IS NOT NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @TYPE IS NOT NULL AND @Location IS NOT NULL AND @Region IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @TYPE IS NOT NULL AND @State IS NOT NULL AND @Region IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Type IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
GO
  
  Note the use the the Delimited 8K Split function, as I noticed that one of your parameters had a list. If you have several parameters you need to supplied delimited string to, I would replace the equals operator with the delimited 8K in each location you need to.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 7:53 am

  #1930968

  heb1014 - Wednesday, March 1, 2017 6:25 AM

  Hi,

  What are you doing to avoid SQL Injection?

  That's all handled by our in-house application.  It's only capable of creating WHERE clauses within very specific parameters.

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 7:59 am

  #1930970

  Thom A - Wednesday, March 1, 2017 7:31 AM

  Personally, I would go with a Stored Procedure and parametrise it. One, problem, however, is that you do have several options, and ideally, you want to use an IF statement to run the relevant query. This means that your SP will be quite bloated, but with a huge dataset, much more effecient.

  I've posted both solutions here, as I'm sure if I only supplied the first, several members of this community will hang me 😉 Yes, the latter solution (with Ifs), has a lot going on, but is the "better" solution. It all depends on what your needs are. If you have a lot more parameters, and not worried about speed, the first option might be "better" for you, but I would do some stress testing first.

  --Using Parameter testing and NULL Statements
CREATE PROC SYSADM.SearchCustomers_NoIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NOT NULL AND @State IS NOT NULL BEGIN --So that at least one parameter is passed

    SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE (C.REGION = @Region OR @Region IS NULL)
  AND (C.[TYPE] = @Type OR @Type IS NULL)
  AND (C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,',')) OR @Location IS NULL)
  AND (C.[STATE] = @State OR @State IS NULL)
  OPTION (RECOMPILE); --As the Query plan could be awful
END

  GO

  --Lots of IF statements
CREATE PROC SYSADM.SearchCustomers_WithIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region;
END
ELSE
IF @Type IS NOT NULL AND @Region IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type;
END
ELSE
IF @Location IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @State IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[TYPE] = @Type;
END
ELSE
IF @Region IS NOT NULL AND @Location IS NOT NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @TYPE IS NOT NULL AND @Location IS NOT NULL AND @Region IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @TYPE IS NOT NULL AND @State IS NOT NULL AND @Region IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Type IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
GO
  
  Note the use the the Delimited 8K Split function, as I noticed that one of your parameters had a list. Have you have several parameters you need to supplied delimited string to, I would replace the equals operator with the delimited 8K in each location you need to.

  We looked at that kind of approach but the complexity of the WHERE clauses just doesn't work for it.  Too many LIKE, NOT LIKE, IN, NOT IN, HAVING, etc.  And that's not even getting started on the use of parentheses...

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 8:00 am

  #1930971

  pietlinden - Tuesday, February 28, 2017 5:01 PM

  Are you connecting to Oracle?

  If you are always going to filter a column, you could use a parameterized stored procedure...
  CREATE PROC GetMyData
     @param1 VARCHAR(20)
  AS
  SELECT {column list}
  FROM MyTable
  WHERE column1 = @param1

  Or you can add filters in your report to do it.  The difference is that having the filters inside the report will bring more data across the network and discard it locally instead of filtering before returning the data to SSRS.

  The queries are simply too complex for that kind of solution.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 1, 2017 at 8:09 am

  #1930973

  frankb 74878 - Wednesday, March 1, 2017 7:53 AM

  heb1014 - Wednesday, March 1, 2017 6:25 AM

  Hi,

  What are you doing to avoid SQL Injection?

  That's all handled by our in-house application.  It's only capable of creating WHERE clauses within very specific parameters.

  Be careful, FrankB... those are famous last words uttered by a whole lot of people that have suffered a breach.  If the app isn't using real parameterization, it's still a risk.  Have someone do some real "attack mode" penetration tests using some of the software that the hackers use for these things.  Better yet, have a 3rd party that specializes in penetration testing check all of your apps, internal or otherwise.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  March 1, 2017 at 8:21 am

  #1930981

  What if they pass in the following parameter?

  ; DELETE FROM SYSADM.CUSTOMERS;

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• heb1014

  Hall of Fame

  Points: 3790

  More actions

  March 1, 2017 at 8:23 am

  #1930982

  I personally would not want to build dynamic SQL without parameterization. 

  Have you considered Power BI?  Power BI has a natural language query feature.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 1, 2017 at 8:35 am

  #1930986

  frankb 74878 - Wednesday, March 1, 2017 7:53 AM

  heb1014 - Wednesday, March 1, 2017 6:25 AM

  Hi,

  What are you doing to avoid SQL Injection?

  That's all handled by our in-house application.  It's only capable of creating WHERE clauses within very specific parameters.

  I agree with Jeff here.  I wouldn't be comfortable with my data being protected in the application layer and not in the data layer.

  Thom A - Wednesday, March 1, 2017 7:31 AM

  Personally, I would go with a Stored Procedure and parametrise it. One, problem, however, is that you do have several options, and ideally, you want to use an IF statement to run the relevant query. This means that your SP will be quite bloated, but with a huge dataset, much more effecient.

  I've posted both solutions here, as I'm sure if I only supplied the first, several members of this community will hang me 😉 Yes, the latter solution (with Ifs), has a lot going on, but is the "better" solution. It all depends on what your needs are. If you have a lot more parameters, and not worried about speed, the first option might be "better" for you, but I would do some stress testing first.

  --Using Parameter testing and NULL Statements
CREATE PROC SYSADM.SearchCustomers_NoIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NOT NULL AND @State IS NOT NULL BEGIN --So that at least one parameter is passed

    SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE (C.REGION = @Region OR @Region IS NULL)
  AND (C.[TYPE] = @Type OR @Type IS NULL)
  AND (C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,',')) OR @Location IS NULL)
  AND (C.[STATE] = @State OR @State IS NULL)
  OPTION (RECOMPILE); --As the Query plan could be awful
END

  GO

  --Lots of IF statements
CREATE PROC SYSADM.SearchCustomers_WithIfs @Region INT = NULL, @Type INT = NULL, @Location VARCHAR(100) = NULL, @State CHAR(2) = NULL
AS

  IF @Region IS NOT NULL AND @Type IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region;
END
ELSE
IF @Type IS NOT NULL AND @Region IS NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type;
END
ELSE
IF @Location IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @State IS NOT NULL AND @Region IS NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @Type IS NOT NULL AND @Location IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[TYPE] = @Type;
END
ELSE
IF @Region IS NOT NULL AND @Location IS NOT NULL AND @Type IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NOT NULL AND @State IS NOT NULL AND @Type IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.REGION = @Region
  AND C.[STATE] = @State;
END
ELSE
IF @TYPE IS NOT NULL AND @Location IS NOT NULL AND @Region IS NULL AND @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[LOCATION] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE
IF @TYPE IS NOT NULL AND @State IS NOT NULL AND @Region IS NULL AND @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @Region IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Type IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
ELSE
IF @Location IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[STATE] = @State;
END
ELSE
IF @State IS NULL BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','));
END
ELSE BEGIN
  SELECT C.CUSTOMER_NUM, C.CUSTOMER_NAME
  FROM SYSADM.CUSTOMERS C
  WHERE C.Region = @Region
  AND C.[TYPE] = @Type
  AND C.[Location] IN (SELECT Item FROM SYSADM.DelimitedSplit8K(@Location,','))
  AND C.[STATE] = @State;
END
GO
  
  Note the use the the Delimited 8K Split function, as I noticed that one of your parameters had a list. If you have several parameters you need to supplied delimited string to, I would replace the equals operator with the delimited 8K in each location you need to.

  I know this has already been rejected, but I thought I'd comment anyway.  The trouble with conditional logic is that all statements are compiled, whether they end up being executed or not.  That means that you could end up with lots of plans in cache that aren't being used.  Worse, the statements that are compiled but not used could be compiled with inappropriate parameter values.  If the statement then ends up being used when the procedure is run again at a later date, that could cause performance problems.  I'd use dynamic SQL, something like this.  It's a technique I learned from Kimberly Tripp.  I've kept it simple by not using the split values thing.

  CREATE PROC SYSADM.SearchCustomers_NoIfs
     @Region int = NULL
,    @Type int = NULL
,    @Location int = NULL
,    @State char(2) = NULL
AS

  DECLARE @sql varchar(max);

  SET @sql = '
    SELECT
         c.CUSTOMER_NUM
    ,    c.CUSTOMER_NAME
    FROM SYSADM.CUSTOMERS
    WHERE 1 = 1
    '
+   CASE
        WHEN @Region IS NULL THEN ''
        ELSE 'AND REGION = @Region '
    END
+   CASE
        WHEN @Region IS NULL THEN ''
        ELSE 'AND TYPE = @Type '
    END
+   CASE
        WHEN @Location IS NULL THEN ''
        ELSE 'AND LOCATION = @Location '
    END
+   CASE
        WHEN @Location IS NULL THEN ''
        ELSE 'AND STATE = @State '
    END
-- You really need to know your data and execution plans for this last bit.
-- The idea is to recompile the statement if the plan is likely to be unstable
-- (ie vary with different values of the parameters). The example below assumes
-- that we expect a stable plan if @Location is specified
+   CASE
        WHEN @Location IS NOT NULL THEN ''
        ELSE 'OPTION (RECOMPILE)'
    END;

  EXEC sys.sp_executesql
     @SQL
,    '@Region int, @Type int, @Location int, @State char(2)'
,    @Region = @Region
,    @Type = @Type
,    @Location = @Location
,    @State = @State;

  John

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 8:47 am

  #1930990

  Jeff Moden - Wednesday, March 1, 2017 8:09 AM

  frankb 74878 - Wednesday, March 1, 2017 7:53 AM

  heb1014 - Wednesday, March 1, 2017 6:25 AM

  Hi,

  What are you doing to avoid SQL Injection?

  That's all handled by our in-house application.  It's only capable of creating WHERE clauses within very specific parameters.

  Be careful, FrankB... those are famous last words uttered by a whole lot of people that have suffered a breach.  If the app isn't using real parameterization, it's still a risk.  Have someone do some real "attack mode" penetration tests using some of the software that the hackers use for these things.  Better yet, have a 3rd party that specializes in penetration testing check all of your apps, internal or otherwise.

  Sorry, but this is 100% in-house and unless someone can get through multiple layers of security to access our network and the gain rights to the report there still isn't any way for them to access any information outside of the view the report is using (REALLY tough for very little gain), this is not a risk.  With the additional layer of having an in-house built application that creates the WHERE clause, there's no chance of SQL injection, since the application won't allow it.  We've been using this approach using Crystal Reports (we're currently migrating to SSRS) for years and it's been very secure.

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 8:50 am

  #1930993

  Eric M Russell - Wednesday, March 1, 2017 8:21 AM

  What if they pass in the following parameter?

  ; DELETE FROM SYSADM.CUSTOMERS;

  The application creating the WHERE statement can't do that.  This isn't a case where a user is writing a WHERE statement, it's an application that allows them to select fields, values, parentheses, etc. The program is very tightly controlled as to what it can write as a WHERE clause.  The user running the program also only has SELECT rights and your example would demand UPDATE rights.

• frankb 74878

  Old Hand

  Points: 316

  More actions

  March 1, 2017 at 8:55 am

  #1930996

  Thanks for all the input, but I think that we've come up with an alternate solution that will work.  We're planning on creating a temp table with fields that match those in the views we're using along an OID, expiration date and a name for the dataset.  The program will write the data to the temp table and then pass in the OID to the SSRS report.  This gives the ability to set an expiration date for the data so that we can save "point in time" data until a specific date.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  March 1, 2017 at 8:58 am

  #1930998

  John Mitchell-245523 - Wednesday, March 1, 2017 8:35 AM

  I know this has already been rejected, but I thought I'd comment anyway.  The trouble with conditional logic is that all statements are compiled, whether they end up being executed or not.  That means that you could end up with lots of plans in cache that aren't being used.  Worse, the statements that are compiled but not used could be compiled with inappropriate parameter values.  If the statement then ends up being used when the procedure is run again at a later date, that could cause performance problems.  I'd use dynamic SQL, something like this.  It's a technique I learned from Kimberly Tripp.  I've kept it simple by not using the split values thing.

  CREATE PROC SYSADM.SearchCustomers_NoIfs
     @Region int = NULL
,    @Type int = NULL
,    @Location int = NULL
,    @State char(2) = NULL
AS

  DECLARE @sql varchar(max);

  SET @sql = '
    SELECT
         c.CUSTOMER_NUM
    ,    c.CUSTOMER_NAME
    FROM SYSADM.CUSTOMERS
    WHERE 1 = 1
    '
+   CASE
        WHEN @Region IS NULL THEN ''
        ELSE 'AND REGION = @Region '
    END
+   CASE
        WHEN @Region IS NULL THEN ''
        ELSE 'AND TYPE = @Type '
    END
+   CASE
        WHEN @Location IS NULL THEN ''
        ELSE 'AND LOCATION = @Location '
    END
+   CASE
        WHEN @Location IS NULL THEN ''
        ELSE 'AND STATE = @State '
    END
-- You really need to know your data and execution plans for this last bit.
-- The idea is to recompile the statement if the plan is likely to be unstable
-- (ie vary with different values of the parameters). The example below assumes
-- that we expect a stable plan if @Location is specified
+   CASE
        WHEN @Location IS NOT NULL THEN ''
        ELSE 'OPTION (RECOMPILE)'
    END;

  EXEC sys.sp_executesql
     @SQL
,    '@Region int, @Type int, @Location int, @State char(2)'
,    @Region = @Region
,    @Type = @Type
,    @Location = @Location
,    @State = @State;

  John

  Thanks John, appreciate the feedback.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk