Scenario: Lets say I have several tables for a software ticketing system: Developer, Supervisor, TicketType, TicketStatus and a Ticket table to join them on. Assume I have done my inner joins and now have a complete table listing all assigned Ticket info.

Id|TicketName|TicketStatus|Developer|Supervisor|DateAssigned| DateDue  |
         1  |  Report  |   Open     |   Josh  |    Bob   | 20130702   | 20130705   
         2  |   App    |   Pending  |   Brian |    Bob   | 20130701   | 20130802
         3  |   App    |   Closed   |   Brian | Hannibal | 20130701   | 2013----  

Obviously the SQL for this is pretty basic:

SELECT TicketId, TicketName, TicketStatus ...
FROM Ticket INNER JOIN TicketType ON [Ticket].TicketID = [TicketType].TicketName
     INNER JOIN [Status] ON .... ....

So that's the DEFAULT static portion of the report. This is what initializes first, however the client, customer, manager, etc. wants the option of selecting zero, one, or more fields to sort data on for all fields in the report. For example:

User may choose 'app' from the TicketName category, 'Josh' as Developer, and BETWEEN date1 AND date2, while TicketStatus Remains NULL or ' '. The table should display all 'app' tickets assigned to 'Josh' between selected dates, while not breaking due to TicketStatus not being selected. Any ticket status should appear.

User could choose not to select any values, in which case the report should default to the example above, without breaking. Meaning I cannot use NULL as a place holder bacause it would cause the initial report to not initialize (unless I did something wrong?)

User can basically mix and match any variation of the fields and the table should sort on those selected fields.

Question: How can someone return a table based on all or no options being selected, without breaking the query?

Additional Info:

Using SQL Server 2008 r2, Business Intelligence Development Studio, T-SQL.

I have tried declaring variables and using LIKE statements, but this requires the user to select an option before the report will return anything. The User may not know what the field values are from memory.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25) 
                                 ,@---Name  varchar(25) 

FROM [Ticket] 
INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID 
INNER JOIN dbo.[Status] ON [Ticket].Stat ....

WHERE [TicketType].Name LIKE @Category
   AND [Ticket].Name LIKE @ReportName 
   AND [Status].StatusName LIKE @Status
   AND [Phase].PhaseName LIKE @PhaseName 
   AND (BA.FullName = @Name1 OR AD.FullName = @Name1)
  • Sorry for the poor spelling and formatting. I am on IE8 and don't have time to Google every mispelled word. Feel free to suggest better ways of presenting the question. Jul 5, 2013 at 18:40
  • You could use dynamic SQL. Without some sort of structure and knowing what RDBMS you are using it's hard to give you a concrete example though. Jul 5, 2013 at 19:07
  • Can you describe for us what the business use case is here? I'm having a bit of difficulty following what's going on.
    – Jon Seigel
    Jul 5, 2013 at 19:10
  • Updated. Hopefully a little clearer, but the Use Case/requirements is/are complex. Jul 5, 2013 at 20:46
  • 1
    Didn't have time to Google? If you had, you would have found Catch All Queries and Dynamic Search Conditions
    – Paul White
    Jul 6, 2013 at 3:06

2 Answers 2


Dynamic SQL is another option. I would try both this method and @a1ex07's method and see which runs faster for you.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25) 
                                 ,@---Name  varchar(25) 

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * ' + CHAR(13) + 
        'FROM [Ticket]  ' + CHAR(13) + 
        'INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID  ' + CHAR(13) + 
        'INNER JOIN dbo.[Status] ON [Ticket].Stat .... ' + CHAR(13) + 
        '..... ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        ' ' + CHAR(13) + 
        'WHERE 1=1 '

IF @Category IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [TicketType].Name LIKE @Category '

IF @ReportName IS NOT NULL
    SET @sql = @sql + CHAR(13) + '  AND [Ticket].Name LIKE @ReportName  '

    SET @sql = @sql + CHAR(13) + '  AND [Status].StatusName LIKE @Status '

    SET @sql = @sql + CHAR(13) + '  AND [Phase].PhaseName LIKE @PhaseName  '

    SET @sql = @sql + CHAR(13) + '  AND (BA.FullName = @Name1 OR AD.FullName = @Name1) '

EXEC sp_executesql @sql, N'@Category varchar(25), @ReportName varchar(25), @Status varchar(25), @PhaseName varchar(25), @Name1 varchar(25)',
                @Category, @ReportName, @Status, @PhaseName, @Name1

I'd do something like

WHERE (@Category IS NULL OR [TicketType].Name LIKE @Category)
AND (@ReportName IS NULL OR [Ticket].Name LIKE @ReportName )
AND ....

Thus, if a particular parameter is null, @Param IS NULL will be true, and filter on this field won't be used...

I hope I understood your question properly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.