Another Reporting System Stored Procedure Issue

Thx to all who helped me for Stored Procedure previously

Here is what I have:
3 Drop Down Boxes:
1) List of property
2) Ticket Status
3) Tech Name

All 3 Drop boxes have default value of "All"

So, if all 3 drop boxes are "All" ie.

list of property = All
Ticket status = All
Tech Name = All

Query pulls up all records from database and displays it.

Lets say if I select Tech Name is XYZ then query should pull out all property, all ticket status by Tech XYZ.

Now my previous developer has if else case and he has total 9 query for doing all this. He has used SQL along with C# code.

I am trying to modify this if-else and convert it into Stored Procedure. Is there a way I can handle all with 1 stored procedure ?

Previous Reporting works like charm......Assuming Property and TicketStatus are ints and TechName is character and assuming that -1 for @.Property and @.TicketStatus means "ALL" and that an empty string for @.TechName means "ALL" then:

CREATE PROCEDURE SprocName
@.Property int = -1,
@.TicketStatus int = -1
@.TechName varchar(20) = ''
Select x,y,x
From SomeTable
WHERE
 (Property= CASE WHEN @.Property != -1 THEN @.Property ELSE Property END) AND
 (TicketStatus = CASE WHEN @.TicketStatus != -1 THEN @.TicketStatus ELSE TicketStatus) END) AND
 (TechName = CASE WHEN @.TechName != '' THEN @.TechName ELSE TechName END)

The where clause resolves to Property = Property if @.Property is -1 which selects all values.
If @.Property is some value like 10 then the where clause resolves to Property = 10.
And so on for the other conditions.|||The poster asked this same question on another thread (view post 421457). My suggestion was similar to yours, but used COALESCE instead:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL
AS
SELECT
 column1,
 column2,
 <etc>
FROM
 myTable
WHERE
 ListOfProperty = COALESCE(@.ListOfProperty,ListOfProperty) AND
 TicketStatus = COALESCE(@.TicketStatus,TicketStatus) AND
 TechName = COALESCE(@.TechName,TechName)

Do you have any idea which approach would be better?

Terri|||Cool. I'll have to try that next time. It makes the code a lot cleaner.|||COALESCE doesn't seem to work. I will try to work with the quesry with COALESCE one more time...

Other solution idea worked perfectly...

One more thing.. do I have to pass -1 and ' ' from my ASP.NET Page which is calling this stored procedure or have to pass null ?|||When there are default parameters, like

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL
AS -- and so on...

If you do not want to pass in a specific value, do not specify a parameter. The default (in this example, NULL) is then used as the value. If you passed in '' and -1, then I expect COALESCE would NOT work as expected.|||CREATE PROCEDURE myTest

@.ListOfProperty varchar(100) = NULL,

@.TicketStatus varchar(100) = NULL,

@.TechName varchar(100) = NULL

In the same procedure can I set up

@.dateofsub smalldatetime = NULL,

so if no dateofsub is not provided, "all dates" are consider else user supplied date is used ?|||With my previous quesion I mean,

How can I setup
@.dateofsub datetime = -1 or NULL or ?

so that either I can have all dates or only user supplied date ?|||Yes, you can add as many conditions as you like. The below should work:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULL,
@.dateofsub smalldatetime = NULL
AS
Select*
FromYourTable
WHERE
 ListOfProperty = COALESCE(@.ListOfProperty,ListOfProperty) AND
 TicketStatus = COALESCE(@.TicketStatus,TicketStatus) AND
 TechName = COALESCE(@.TechName,TechName) AND
 dateofsub = COALESCE(@.dateofsub,dateofsub)
GO

|||my problem is some how COALESCE is not working for me and I am trying to use if else example given ...

When I use SQL Query Analyzer and to test my stored procedure, it doesn't work with datetime I supply.

Here is what I am doing for eg.

List of property = null ( for all property)
TicketStatus = Open
TechName = XYZ
dateofsub = 10/14/2003

SO when I call stored procedure from SQL Query Analyzer

exec myTest null, 'Open', 'XYZ', '10/14/2003'

and it gives me all dates in stead of only 10/14/2003...

Any idea ?|||Here it is in the alternative syntax:

CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = '',
@.TicketStatus varchar(100) = '',
@.TechName varchar(100) = '',
@.dateofsub smalldatetime = '1/1/1970'
AS
Select*
FromYourTable
WHERE
 ListOfProperty = CASE WHEN @.ListOfProperty != '' THEN @.ListOfProperty ELSE ListOfProperty END AND
 TicketStatus= CASE WHEN @.TicketStatus != '' THEN @.TicketStatus ELSE TicketStatus END AND
 TechName = CASE WHEN @.TechName != '' THEN @.TechName ELSE TechName END AND
 dateofsub= CASE WHEN @.dateofsub!= '1/1/1970' THEN @.dateofsub ELSE dateofsub END

But the COALESCE should have worked too. If you'd like, post the code and we .can see if it's missing something.|||I am calling this stored procedure from Web Service.

Lets say if I pass null for Date from Web Service it gives me error...

What should I do ?

Here is how call stored procedure from Web Service

public DataSet GetHelpDeskReports(int pid, string status, System.DateTime dateofsub, string techName) {

SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand myCommand = new SqlCommand("sp_trial", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameterPID = new SqlParameter("@.pid", SqlDbType.Int, 4);
parameterPID.Value = pid;
myCommand.Parameters.Add(parameterPID);

SqlParameter parameterStatus = new SqlParameter("@.status", SqlDbType.VarChar, 100);
parameterStatus.Value = status;
myCommand.Parameters.Add(parameterStatus);

SqlParameter parameterDateOfSub = new SqlParameter("@.dateofsub", SqlDbType.DateTime, 8);
myCommand.Parameters.Add(parameterDateOfSub);

SqlParameter parameterTechName = new SqlParameter("@.techName", SqlDbType.VarChar, 100);
parameterTechName.Value = techName;
myCommand.Parameters.Add(parameterTechName);

SqlDataAdapter myDataAdapter = new SqlDataAdapter();
DataSet myDataSet = new DataSet();

// Open the connection and execute the Command
try {
myConnection.Open();
myDataAdapter.SelectCommand = myCommand;
myDataAdapter.Fill(myDataSet);
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message.ToString());
}
finally
{
myConnection.Close();
}

return myDataSet;
}

Now System.DateTime dateofsub in Web service can be null... If I keep it null I get error when I try to run web service.

How am I suppose to check in Web Service that parameter I am passing to SQL Stored Procedure is NULL or not...

As per discussion................................ I don't have to check in my Web Serivce coz SQL Stored Procedure handles null by means of If.. else or COALESCE...

Why I am getting Error!!!!

I tired '', "", null and ever not inputing anything in dateofsub.. in all cases I get error...|||I tried COALESCE and If Else...

My query works fine till I don't have dateofsub in my Stored Procedure.

as soon as I put
@.dateofsub datetime = null

and then call my Stored Procedure from SQL Query Analyzer

exec sp_trial null, null, '3/9/2003'

I should be getting few rows as I have data, but I don't get any rows.... if I pass null for dateofsub query runs fine and returns all rows....

Whats wrong ??|||With dates it's always something...

The problem may be that the dates in the table for 3/9/2003 have a time component and the query is implicitely asking for 3/9/2003 00:00:00.0.

I've gotten around that problem by creating a function to truncate dates to midnight.

CREATE FUNCTION TruncateDate(@.DATE1 datetime)
RETURNS datetime
AS
BEGIN
DECLARE @.TruncatedDate datetime
Select @.TruncatedDate =null
if @.Date1 != null
BEGIN
Select @.TruncatedDate =Convert( varchar, @.DATE1, 101)
END
RETURN(@.TruncatedDate)
END

and modify your code in the sproc to use the rounded date field instead of the original:

dbo.TruncateDate(dateofsub) = COALESCE(dbo.TruncateDate(@.dateofsub ),dbo.TruncateDate(dateofsub ))

|||Got it.. You are right, I have to check time part also.. I will try to modify my Database, as I really don't need time part...

I will try to use smalldatetime as DataType...

ANother thing is, I am calling this stored procedure from C# code. At different time, I have different drop down box selected.

If I select "ALL" in drop down box, I pass "-1". Will -1 will work with dates ? or I need to have null or '' ?