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) = NULLAS
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) = NULLAS -- 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 '' ?
