I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.
ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)OR (PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)OR (StatusISNULL)AND (ContactTypeID = @.ConTypeID)OR (ContactTypeIDISNULL)AND (Caller = @.Caller)OR (CallerISNULL)AND (SubjectLIKE @.Keyword)OR (SubjectISNULL)RETURN
You have the query correct. Your OR's and AND's are misplaced around the brackets.
ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASBEGINSET NOCOUNT ONSELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROMtblTasksWHERE(ClientID = @.ClientID)AND (Priority = @.PriorityOR @.PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.StatusOR @.StatusISNULL)AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)AND (Caller = @.CallerOR @.CallerISNULL)AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)SET NOCOUNT OFFEND|||
I tried it and I'm still not getting any rows returned. I have even tried it with all parameters having a good entry.
Just to be sure;
I should be able to enter a clientID, an ActionDate range, a priority, and the other fields of the table could have any entry or null for the other parameters and get returned, YES?
|||I did not notice it but try setting the length for your parameters.|||Besize the size, could the default values be the reason:
Like:
...
@.Statusnvarchar(50)=NULL,
@.ConTypeIDint=NULL,
@.Callernvarchar(50)=NULL,
@.Keywordnvarchar(50)=NULL
I really am stuck on this one. Can anyone offer any suggestions? Does anyone understand my problem with this?
|||
Hi
You could add default value to parameter as limno suggested.
If that doesn't work. You could try adding following code to your stored procedure and test in Sql Server Management Studio to trace each parameters.:
if (@.PriorityISNULL)begin print N'Priority IS NULL'endelsebegin print @.Priorityend--print other parameters in similar wayHope this helps.|||
So here is what I have, listed below: I should be able to pass in a clientID and a valid date range and it should not matter what is in the other fields because I'm passing in NULL, right?
ALTER PROCEDURE dbo.eP_BindContactManagementAction(@.ClientIDint,@.Prioritysmallint=NULL,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar(50)=NULL,@.ConTypeIDint=NULL,@.Callernvarchar(50)=NULL,@.Keywordnvarchar(50)=NULL)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)AND (ContactTypeID = @.ConTypeID)AND (Caller = @.Caller)AND (SubjectLIKE @.Keyword)RETURNI appreciate any help,|||
You WHERE clause has problem to handle NULL values: Please try this one fromndinakar
SELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,
ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROMtblTasks
WHERE(ClientID = @.ClientID)
AND (Priority = @.PriorityOR @.PriorityISNULL)
AND (ActionDateBETWEEN @.TStartAND @.TEnd)
AND (Status = @.StatusOR @.StatusISNULL)
AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)
AND (Caller = @.CallerOR @.CallerISNULL)
AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)
|||
Another question: could you show us your datasource control code too? if you are using SQLDatasource control, you may need to set this CancelSelectOnNullParameter="false".
Just another shot in dark.
|||Here is the code, it will return a record if I make an entry for all parameters. But if I leave one blank no records are returned.
Sub BindData() Session("TaskStart") =Me.BasicDatePickerHStart.SelectedValue Session("TaskEnd") =Me.BasicDatePickerHEnd.SelectedValueDim TStartAs String = Session("TaskStart")Dim TEndAs String = Session("TaskEnd")Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("SQL2ConnectionString").ConnectionString'If user unchecks use due date sproc.Dim strSprocAs String strSproc ="BindContactManagementAction" MyConnection =New SqlConnection(ConnectStr) MyCommand =New SqlCommand(strSproc, MyConnection) MyCommand.CommandType = CommandType.StoredProcedureDim ClientIDParamAs New SqlParameter("@.ClientID", SqlDbType.Int, 4) MyCommand.Parameters.Add(ClientIDParam) ClientIDParam.Value = Session("lgClientID")Dim TaskStartParamAs New SqlParameter("@.TStart",Me.BasicDatePickerHStart.SelectedValue) MyCommand.Parameters.Add(TaskStartParam) TaskStartParam.Value =Me.BasicDatePickerHStart.SelectedValueDim TaskEndParamAs New SqlParameter("@.TEnd",Me.BasicDatePickerHEnd.SelectedValue) MyCommand.Parameters.Add(TaskEndParam) TaskEndParam.Value =Me.BasicDatePickerHEnd.SelectedValueIf String.IsNullOrEmpty(Me.KeyWordText.Text)Then Dim KeywordParamAs New SqlParameter("@.Keyword", DBNull.Value) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)Else Dim KeywordParamAs New SqlParameter("@.Keyword",Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then Dim StatusParamAs New SqlParameter("@.Status", DBNull.Value) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)Else Dim StatusParamAs New SqlParameter("@.Status",Me.StatusSearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)End If If String.IsNullOrEmpty(Me.PrioritySearchDrop.SelectedItem.Text)Then Dim PriorityParamAs New SqlParameter("@.Priority", DBNull.Value) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)Else Dim PriorityParamAs New SqlParameter("@.Priority",Me.PrioritySearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)End If If String.IsNullOrEmpty(Me.CallerTextSearch.Text)Then Dim CallerParamAs New SqlParameter("@.Caller", DBNull.Value) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)Else Dim CallerParamAs New SqlParameter("@.Caller",Me.CallerTextSearch.Text) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)End If If String.IsNullOrEmpty(Me.ContactTypeSearchDrop.SelectedValue)Then Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID", DBNull.Value) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)Else Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID",Me.ContactTypeSearchDrop.SelectedValue) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)End If MyConnection.Open()Dim DSAs SqlDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) MyEditDataGrid.DataSource = DS MyEditDataGrid.DataBind()End Sub|||We have to find where the problem is:
1. Whether the Stored Procedure is running OK or not in your database?
2. If the sp can return records with limited parameters, we can focus on the code part.
PS:(this is a version I tested on my db which works)( By the way, I would use field name to Name parameters to avoid confusion, but this is not the problem here)
ALTER
PROCEDURE [dbo].[eP_BindContactManagementAction](
@.ClientID
int,@.Priority
smallint=NULL,@.TStart
datetime,@.TEnd
datetime,@.Status
nvarchar(50)=NULL,@.ConTypeID
int=NULL,@.Caller
nvarchar(50)=NULL,@.Keyword
nvarchar(50)=NULL)
AS
SELECT
Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,ReminderDate,Reminder
, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM
tblTasksWHERE
(ClientID= @.ClientID)AND
(Priority= @.PriorityOR @.PriorityISNULL)AND
(ActionDateBETWEEN @.TStartAND @.TEnd)AND
(Status= @.StatusOR @.StatusISNULL)AND
(ContactTypeID= @.ConTypeIDOR @.ConTypeIDISNULL)AND
(Caller= @.CallerOR @.CallerISNULL)AND
(SubjectLIKE @.KeywordOR @.KeywordISNULL)END
|||Ok, progress the sp is working in the database, I tested it in VS 2005 and it worked, so it must be in my code.
Any thoughts from my earlier post?
|||I changed all my parameters to the code type below and it is working now:
I very much appreciate your help!
If String.IsNullOrEmpty(Me.KeyWordText.Text)Then MyCommand.Parameters.AddWithValue("@.Keyword", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Keyword",Me.KeyWordText.Text)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then MyCommand.Parameters.AddWithValue("@.Status", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Status",Me.StatusSearchDrop.SelectedItem.Text)End Ifsql
