Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
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. –  Josh Campbell Jul 5 '13 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. –  Kenneth Fisher Jul 5 '13 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 '13 at 19:10
    
Updated. Hopefully a little clearer, but the Use Case/requirements is/are complex. –  Josh Campbell Jul 5 '13 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 '13 at 3:06
add comment

2 Answers

up vote 1 down vote accepted

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  '

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

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

IF @Name1 IS NOT NULL
    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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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