I have a stored procedure as a recordsource from a contacts table. In
this example, users can enter parameters to limit contacts by first
letter of last name or company name or keywords:
Example:
@myName nvarchar(30) = null,
@Alpha char(1) = null
SELECT
Contacts.Contac tID, ContactType,
CASE WHEN Contacts.Contac tType = 0
THEN Contacts.Compan yName
ELSE
isNull(Contacts .LastName,'?') + ', ' +
isNull(Contacts .FirstName,'?')
END
AS CNAME
FROM
Contacts
WHERE
(Keywords Like '%' + @myKeyword + '%' OR @myKeyword is Null)
So far, so good, but...
The problem is I want to also give the user the option of filtering
alphabetically by first letter. I can't figure out how to deal with
nulls in this example (user doesn't enter anything as parameter
@Alpha):
AND
(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1 ) END
OR
@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyNam e,1) END)
Any help is appreciated,
LQ
2  1613 
Lauren Quantrell (la************ *@hotmail.com) writes: The problem is I want to also give the user the option of filtering
alphabetically by first letter. I can't figure out how to deal with
nulls in this example (user doesn't enter anything as parameter
@Alpha):
AND
(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1 ) END
OR
@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyNam e,1) END)
What about :
@Alpha = CASE ContactType
WHEN 0 THEN Left(LastName, 1)
WHEN 1 THEN Left(CompanuNam e, 1)
END
OR @Alpha Is NULL
Beware that if you are hoping for any sort of index usage, this is not
going to fly at all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland,
Thank you. I didn't think that one through enough!
lq
Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* **************@ 127.0.0.1>... Lauren Quantrell (la************ *@hotmail.com) writes: The problem is I want to also give the user the option of filtering
alphabetically by first letter. I can't figure out how to deal with
nulls in this example (user doesn't enter anything as parameter
@Alpha):
AND
(@Alpha = CASE ContactType WHEN 0 THEN Left(LastName,1 ) END
OR
@Alpha = CASE ContactType WHEN 1 THEN Left(CompanyNam e,1) END)
What about:
@Alpha = CASE ContactType
WHEN 0 THEN Left(LastName, 1)
WHEN 1 THEN Left(CompanuNam e, 1)
END
OR @Alpha Is NULL
Beware that if you are hoping for any sort of index usage, this is not
going to fly at all. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Eric Lilja |
last post by:
Hello, consider the following complete program:
#include <assert.h>
#include <ctype.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <time.h>
static int has_char(const char *, const char);
|
by: Max Sandman |
last post by:
I'm getting increasingly frustrated with C# and its exceptions on null
values. Rather than try to deal with it on a hit-or-miss basis as
exceptions pop up, I thought I should try to learn exactly how C# deals
with null. Of course, there's nothing obvious in the docs like "Dealing
with Null Values" and a search on "null" yielded 500 results, most of
which don't apply. Can anybody point me in the right direction? Or offer
some general...
|
by: Rene |
last post by:
I have my object model thing going with some classes that contain properties
that are saved into a database. Some of these properties are *numeric*
properties that can be left Null in the database table.
My problem is that there does not seem to be a way to assign a Null value to
your typical numeric value type variable.
|
by: PK |
last post by:
Hi, I have an application that opens a Crystal report document and
passes in a value to a parameter in the report (pointing to an Oracle
DB). However, if I want to pass a "null" value to retrieve all records
that have no data for the specified field that the parameter is a
condition of (ie. SELECT * from <table> where <field> is null"), it
returns nothing. I think CR is building the SQL statement like "SELECT
* from <table> where <field>...
|
by: Mark A |
last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).
Using the SAMPLE database, tables EMP and EMLOYEE.
In the followng stored procedure, 2 NULL columns (COMM) are selected into 2
different SP variables and compared for equal. They are both NULL, but do
not compare as equal. When the Not NULL columns (SALARY) are compared, they
do compare as equal.
| | |
by: GaryDean |
last post by:
Is there any good way in which to deal with null datetimes. For instance, I
can't seem to find anything that will pass a null value in a datetime
parameter to another method. Convert.dbnull will not work for instance.
Getting nulls back and forth between C# and SQL Server is always
problematic.
--
Regards,
Gary Blakely
|
by: MikeT |
last post by:
This may sound very elementary, but can you trap when your object is
set to null within the object?
I have created a class that registers an event from an object passed
in the constructor. When my object is destroyed, I want my object to
un-register this event. If I don't then the object would never be
destroyed until the object I passed in the constructor is destroyed.
I have implemented a Dispose(), Dispose(bool), and ~Finalize...
|
by: valentin tihomirov |
last post by:
As explained in "Using pointers vs. references"
http://groups.google.ee/group/borland.public.delphi.objectpascal/browse_thread/thread/683c30f161fc1e9c/ab294c7b02e8faca#ab294c7b02e8faca ,
the pointers are allowed to be null, while references must refer an existing
variable of required type. The null is normally used for making optional
parameters. But there is no way to pass null reference in C#. Something is
missing.
|
by: justice750 |
last post by:
Hi All,
I am using a FormView control. The allows me to update records in the database. However, when a database field is null I can not update the field on the form. It works fine when the field is not a null value. I am not using any code behind (C#) to bind the data or manipulate the data.
I have read that when there is a null value in the database that there is no record in the "dataset". Can anyone show me how to bind a value in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
