I intend to make a stored procedure that accepts several parameters and that has an option not to accept any parameters or some paramters.
I have made the default value null in all cases.
The Problem is that when I test it with more that one parameter which is not the default it does not work or gets unconsistent results
any ideas.
Would be mostly appreciated
Thank You
David
I am first attempting to write a query that does the action of the stored procedure first.
The query is below;
Declare @TicketNo bigint = null
Declare @Group_Assignment nvarchar(50) = null
Declare @status nvarchar(50) = null
Declare @Urgency nvarchar(50) = null
Declare @Ticket_Category nvarchar(50) = null
Declare @Requestor nvarchar(50) = null
Declare @Closure_Reason nvarchar(50).. = null
select GroupName, T.Status, T.TicketNo, T.Requestor, T.TicketCategory, U.FirstName + ' ' + U.LastName + ' (' + convert(varchar,U.SamAccountName) + ')' as 'Technician',
T.TicketCreatedDate, DATEDIFF(DAY, T.TicketCreatedDate, getdate()) as 'DAys Open'
From DB.dbo.HelpDesk_Ticket T
join DB.HelpDesk_Groups G on T.AssignedGroupID = G.ID
join DB.VW_AD_ADUsers U on U.Guid = T.AssignedTechGUID
where
(
((TicketNo = @TicketNo) or @TicketNo is null) and
((GroupName = @Group_Assignment) or @Group_Assignment is null) and
((Status = @status) or @status is null) and
((Urgency = @Urgency) or @Urgency is null) and
((Ticket_Category = @Ticket_Category) or @Ticket_Category is null) and
((Requestor = @Requestor or @Requestor is null) and
((ClosureReason = @Clusure_Reason) or @Clusure_Reason is null)
) option (RECOMPILE)