concatenation 1

[mikmak]

hi,
I have a problem with my report format.
I use stored procedure to generate a report from some templates, which contain many input variables (ex. A, B, C).

The report should look like:
A, B, and C.

Currently, my condition is:
CASE WHEN @A != '' THEN @A + ',' + SPACE(1) ELSE '' END+
CASE WHEN @B != '' THEN @B + ',' + SPACE(1) ELSE '' END+
CASE WHEN @C != '' THEN @C + ',' + SPACE(1) ELSE '' END

then, I call a concatenate procedure to replace the codes(from right to left):
comma (,) -> dot (.)
comma (,) -> and

so, the previous format: A, B, C, will be changed to A, B and C.

The concatenate procedure will give a problem when I have to get an output like:
(A, B and C) and D.

I use temporarily storage (@temp1) to concatenate A,B,C, to become A, B and C.
Then, I concatenate @temp1 with D to give an output: (A,B and C) and D.
The problem occurs when D is NULL.
The output that I get if D is NULL: (A and B and C).

do you have any idea how to solve this problem ?

thanks in advance
John NS

 

[rajeevnandanmishra]

Hi john,
Once you get A, B and C. Then something like this can be done:
DECLARE @mainOutput Varchar(50)
SELECT @mainOutput="A, B and C."
SELECT @mainOutput="("+SUBGSTRING(@mainOutput,1,DATALENGTH(@mainOutput)-1)+&quot."+CASE WHEN D is Null THEN "" ELSE " and "+D+"."
SELECT @mainOutput

Let us know if you face further problem in this regard.