Store Procedures, SQL statements

pts.
Tags:
Crystal Reports
Microsoft Windows
SQL Server
I am a newby to SQL and I am currently creating a store procedures for Crystal Reports. I have ran into a Problem. Here is some of my coding: SELECT @Address = ISNULL(Address,'') from tblAddresses WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Current') IF LTRim(RTrim(@Address)) <> '' SELECT @Address = isnull(Address,'') + char(13) + char(10) + isnull(City,'') + ', ' + isnull(State,'') + ' ' + isnull(Zip1,'') FROM tblAddresses WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Current') ELSE SELECT @Address = isnull(Address,'') + char(13) + char(10) + isnull(City,'') + ', ' + isnull(State,'') + ' ' + isnull(Zip1,'') FROM tblAddresses WHERE (ClientNumber = @ClientNumber) AND (AddressType = 'Mailing') I must have Current Address and if there is no Current Address I must get the mailing address. I keeping getting null values. I have tried it many different ways and result is null. In AddressType I have 3 different criterias but I only need 2 in this stored procedures. Can anyone help?

Answer Wiki

Thanks. We'll let you know when a new response is added.

For efficiency, you want to try to do this all in a single query, without variables and IFs. Don’t return CRLFs to Crystal. Let Crystal do the formatting. Process as follows:

SELECT CASE WHEN current.address IS NULL THEN
mailing.address ELSE current.address END address,
CASE WHEN current.address IS NULL THEN mailing.city ELSE
current.city END city,
CASE WHEN current.address IS NULL THEN mailing.state
ELSE current.state END state,
CASE WHEN current.address IS NULL THEN mailing.zip1 ELSE
current.zip1 END zip1,
mailing.clientNumber
FROM tblAddresses mailing
LEFT OUTER JOIN tblAddresses current ON
current.addressType=’Current’ AND
mailing.clientNumber=current.clientNumber AND
LENGTH(TRIM(current.address))>0
WHERE mailing.addressType=’Mailing’

You can set this up in Crystal by drawing a connection, and then selecting it, then LEFT. You can get everyone’s address at once with this, or add a criterion, and get just one.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following