0

i got an apps that called stored procedure to get result. the input from the apps is like this:

  1. Group - nullable
  2. Name - nullable. if i fill Name, ID no need to be filled
  3. ID - nullable. if i fill ID, Name no need to be filled
  4. Segment - not nullable. the input can be ALL segment
  5. Period - not nullable

The "WHERE" possibilities that thinking of me is 12 possibilities.

  1. Segment, Period
  2. Segment, Period, ID
  3. Segment, Period, Name
  4. Segment, Period, Group
  5. Segment, Period, Group, ID

  6. Segment, Period, Group , Name

  7. ALL Segment, Period

  8. ALL Segment, Period, ID
  9. ALL Segment, Period, Name
  10. ALL Segment, Period, Group
  11. ALL Segment, Period, Group, ID
  12. ALL Segment, Period, Group, Name

and the example query is like this:

IF @Group IS NOT NULL AND @ID IS NOT NULL AND @Segment = 'KIDS'
BEGIN
   SELECT * FROM mytable
   WHERE Group = @Group AND ID = @ID AND Segment = @Segment
END

Is there any way to shorten the possibilities, so the query is less to be written?

Thank you

abuybuy
  • 799
  • 2
  • 16
  • 33
  • 6
    You should use dynamic SQL. Read this articles: [Catch-all Queries by Gail Shaw](http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) and [Dynamic Search Conditions by Erland Sommarksog](http://www.sommarskog.se/dyn-search-2008.html). – Felix Pamittan Jul 25 '15 at 04:32
  • Catch all won't work as he is not free-searching. For example there is no case for ID and Name being searched together. OP will have to clarify if `@ID` and `@Name` are specified together then what will happen. With logic like this dynamic SQL is the only way to go. – Alan Jul 25 '15 at 21:49
  • Also see [the kitchen sink](http://sqlsentry.tv/the-kitchen-sink-procedure/). – Aaron Bertrand Jul 27 '15 at 04:35

2 Answers2

0

The answer is like this. I write down some of the code

DECLARE @query nvarchar(1000), @where nvarchar(1000), @Group varchar(10), @ID varchar(10), @Segment varchar(10)
SET @where = ''
SET @query = 'SELECT * FROM mytable'

IF @Group IS NOT NULL
SET @where = @where + ' AND Group = @_Group '

IF @ID IS NOT NULL
SET @where = @where + ' AND ID = @_ID '

IF @Segment IS NOT NULL
SET @where = @where + ' AND Segment = @_Segment '

IF LEN(@where) > 0
SET @query = @query + ' WHERE ' + RIGHT(@where, LEN(@where)-3)

I have set variable @where with empty string. Because if i don't, @where can't be concated because the default value of @where is null

use NVARCHAR instead of VARCHAR for the query

Call the @query like:

EXECUTE sp_executesql @query, N'@_Group varchar(100), @_ID varchar(100), @_Segment varchar(100)', @_Group = @Group, @_ID = @ID, @_Segment = @Segment
abuybuy
  • 799
  • 2
  • 16
  • 33
  • 1
    That's very poor for security reasons (see SQL injection). You should leave the parameters as parameters in the generated SQL. Do not try to expand them to values. you can fix this by removing most of the triple quotes and pass in parameters. http://stackoverflow.com/questions/1036745/t-sql-how-to-use-parameters-in-dynamic-sql – LoztInSpace Jul 27 '15 at 02:54
  • yeah, i know. but i think it's okay because the input from the client is not a textbox, but dropdownlist. – abuybuy Jul 27 '15 at 03:50
  • 1
    Even if that's true, why would you? It may not be true in the future, it may not even be true now. Also, by using parameters the query plan can be used again for the same combination of inputs. Also using parameters enforces type-safety and sidesteps silent conversion related performance problems. In short, there's no reason to not use parameters. – LoztInSpace Jul 27 '15 at 03:57
  • you brighten me up. i am really afraid to conversion problem in SQL. Lemme change my query above. – abuybuy Jul 27 '15 at 04:23
0

A non-dynamic SQL version:

SELECT * 
FROM mytable 
WHERE 1 = 1
    AND (Period = @Period)
    AND (@Segment = 'ALL' OR Segment = @Segment)
    AND (@Group IS NULL OR Group = @Group)
    -- Assume one of @Name or @Id is not null
    AND (@Id IS NULL OR Name = @Name)
    AND (@Name IS NULL OR Id = @Id)
Eric
  • 5,675
  • 16
  • 24
  • 1
    Trouble with this approach is that you generally get one query plan (the first) to try and satisfy every combination encountered. I'm generally not a fan of dynamic SQL but in these cases it often makes sense, so long as you don't overlook the parameter part. You get one plan per combination, which is generally what you're after. – LoztInSpace Jul 27 '15 at 03:59
  • @LoztInSpace Yes, this type of query may not get optimize plan as there are a lot's `OR` in the where condition. – Eric Jul 27 '15 at 06:18