Help with SQL Function - Cant change Null value

What I'm trying to do is to build a string that will print all the presences of a user for a session. My problem is that I'd like to put the - value when the pr_presence isn't True or False but right now it only returns pr_presence when it contains a boolean value. This way I can't treat the NULL or blank value. Any help would be really appreciate !

Here is my fonction:

DECLARE strPresence VARCHAR;
sessionID alias for $1;
userID alias for $2;
idr record;
BEGIN
strPresence := '';
For idr in
SELECT CASE When pr_presence = 't' Then 'P'
When pr_presence = 'f' Then 'A'
Else '-'
End as "TypePresence"
FROM seance, InscriptionEtat, inscriptionSession
RIGHT JOIN presence ON inscriptionSession.usr_id = presence.usr_id
LEFT JOIN session ON inscriptionSession.ses_id = session.ses_id
WHERE session.ses_id = sessionID
AND presence.usr_id = userID
AND presence.sea_id = seance.sea_id
AND seance.ses_id = session.ses_id
AND seance.sea_valide = 't'
AND inscriptionSession.usr_id = usager.usr_id
AND inscriptionSession.ie_id = inscriptionEtat.ie_id
AND inscriptionEtat.ie_OK = 't'
ORDER BY seance.sea_datedebut
LOOP
strPresence:= strPresence||', '||idr."TypePresence";
END LOOP;
strPresence:= substring(strPresence,char_length(', ')+1);
RETURN strPresence;
END;I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -|||Originally posted by andrewst
I don't quite understand your problem. The CASE statement works OK:

SQL> SELECT pr_presence, CASE When pr_presence = 't' Then 'P'
2 When pr_presence = 'f' Then 'A'
3 Else '-'
4 End as "TypePresence"
5 FROM seance;

P T
- -
t P
f A
-
x -

If my session contains 4 seances, and the user only enters 1 presence for these seances, my string should looks like "P,-,-,-" because the other 3 pr_presence would be Null
Right now, my string is returning "P" when I test it... I also tought my case was ok but I'm now wondering why I don't get what I want. Thanks for your help

Help with Sproc and multi parameter

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 set the size and still I must have an entry in every parameter. Am I missing something? I should be able to do this, right?|||

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)RETURN

I 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, DueDate

FROM

tblTasks

WHERE

(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 If

sql

Help With Query

Hi All,

I am trying to build a report where show all the numbers of orders completed and orders which are completed on time. However, I have a hard time to figure out how to build the where clause since I want to get all the completed orders and at the same time, I also want to count the completed orders on time. Can anyone help me with this query?

This is my query right now:

SELECT COUNT(o.OrderID)
FROM Order o
WHERE MONTH(o.InvoiceDT) = @.Month

TIA

hi,

first of all is there any field like order_to_be_finished_by (date) & finished_on(data) fields or not in your table.

Select Count(OrderID) As OrdersCompletedOnTime from table

where convert(varchar, to_be_Finished_Date, 103) = convert(varchar, finished_on, 103)

& second query

Select Count(OrderID) As OrdersCompletedOnTime from table

where finished_on is not null

hope it helps

regards,

satish.

|||

Thank you satish,

So, it is would be two different select statement in one single stored procedures.

|||

yep,

regards,

satish

Help with Query

I am struggling to build the appropriate query for the following scenario. A
simplified version of my problem is... consider the following table
EEID EPID StartDate
1 11 1/1/2005
1 13 2/1/2005 *
2 13 1/1/2005 *
3 14 2/1/2005
3 11 4/1/2005 *
The star indicates the records that should be pulled, and I need all 3
fields. The condition is, for each EEID pull the record with the largest
date.
If i do something like
select EEID, max(StartDate) from table group by EEID
I get the appropriate row, but i am missing one field.
Please help. I have struggled long and hard on this one.
thank You.
PooravI am , what field are you missing?
Message posted via http://www.webservertalk.com|||Select * From Table T
Where StartDate = (Select Max(StartDate)
From Table
Where EEID = T.EEID)
"haiiyaa" wrote:

> I am struggling to build the appropriate query for the following scenario.
A
> simplified version of my problem is... consider the following table
> EEID EPID StartDate
> 1 11 1/1/2005
> 1 13 2/1/2005 *
> 2 13 1/1/2005 *
> 3 14 2/1/2005
> 3 11 4/1/2005 *
> The star indicates the records that should be pulled, and I need all 3
> fields. The condition is, for each EEID pull the record with the largest
> date.
> If i do something like
> select EEID, max(StartDate) from table group by EEID
> I get the appropriate row, but i am missing one field.
> Please help. I have struggled long and hard on this one.
> thank You.
> Poorav
>

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations ??
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.com
E B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations ??
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.comE B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP
--

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
--
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.sqlmonster.comE B via SQLMonster.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.sqlmonster.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
--
David Portas
SQL Server MVP
--