Concatenated string as parameter

Posted on 2012-09-21
Medium Priority
Last Modified: 2012-11-05
I have a concatenated field that includes Address fields and correspondence address fields.

e.g. addressline1+' '+addressline2+' '+postcode+' '+correspondence_address1+' '+correspondence+address2+' '+correspondence_postcode as fulladdress

This field is used as a parameter in a report.  users must enter a character when prompted.  The problem I am having is that sometimes a correspondence address is null but the addressline1 and addressline2 would always have a value.

As an example if I enter 'b' in the search test box and  'fulladdress'  contains 'b', data is returned but when correspondence_address1+' '+correspondence+address2+' '+correspondence_postcode  is null, no data is returned

I have tried replacing the null value with spaces but this has not worked.

Please can anyone suggest a ny wok arounds?  

Question by:turaks
  • 2
LVL 18

Expert Comment

by:Jerry Miller
ID: 38424044
I wouldn't use a concatenated field as a parameter. Instead use all of them as individual parameters and allow them to be null.

@addressline1 vachar(50) = null for example in the declaration.

Then use where addressline1 = ISNull(@addressline1, address line) for each variable

This gives the flexibility for any of the possible null values to be ignored in the query.

Author Comment

ID: 38425515
Thanks for your response.

Going by your suggestion means that I would have 10 prompts.  It wouldn't look neat having to enter the same search text in 10 boxes.
LVL 18

Expert Comment

by:Jerry Miller
ID: 38425623
Are your users entering all of this data in 1 textBox? Can you provide your code?

I am talking about having 10 separate variables where you can test each one for null before continuing.
LVL 37

Accepted Solution

ValentinoV earned 1500 total points
ID: 38428332
If you concatenate varchar fields in T-SQL, you need to take NULLs into account.  Use the COALESCE function on each field that can be NULL:

SELECT COALESCE(addressline1+' ','') + COALESCE(addressline2+' ', '')
+ COALESCE(postcode+' ','') + COALESCE(correspondence_address1+' ','')
+ COALESCE(correspondence_address2+' ','')
+ COALESCE(correspondence_postcode,'') as fulladdress

Open in new window

Coalesce: http://msdn.microsoft.com/en-us/library/ms190349.aspx

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question