SQL Stored Procedure How to Modify and Return the Result of an Executed Dynamic Query

Question

I have created a stored procedure that returns a single string of concatenated fields. The issue is that some of these fields may be empty strings resulting in a string much like the below:

, Mendip Road, Farnborough, Hampshire, GU14 9LS

or even

, , Farnborough, Hampshire, GU14 9LS

I really want to strip off any leading commas but I'll only know this once the query has been executed. Is there a way of executing the query, pattern-matching the commas and then removing them before finally returning the modified string?

The query itself is as follows:

SET @SQLQuery = 'SELECT TOP 1 REPLACE((ISNULL(POI,'''') + '', '' + ISNULL(Name,'''') + '', '''
+ ' + ISNULL(Settlement,'''') + '', '' + ISNULL(Cou_Unit,'''') + '', '' + ISNULL(Postcode,'''')),'', , '', '', '')'
+ ' AS ClosestAddress FROM [UKStreetsAndPlaces].[dbo].[OS_Locator] ORDER BY '
+ ' (Longitude ' + @LongitudeOperator + ' ' + CAST(ABS(@Longitude) AS VARCHAR(20)) + ')'
+ ' * (Longitude ' + @LongitudeOperator + ' ' + CAST(ABS(@Longitude) AS VARCHAR(20)) + ')'
+ ' + (Latitude - ' + CAST(@Latitude AS VARCHAR(20)) + ') * (Latitude - ' + CAST(@Latitude AS VARCHAR(20)) + ') ASC'


EXECUTE(@SQLQuery)

Answer 1

Concatenate the comma inside the ISNULL expression as follows:

ISNULL(POI + ', ','')

so your query will look like:

SET @SQLQuery = 'SELECT TOP 1 REPLACE((ISNULL(POI + '', '','''') + ISNULL(Name + '', '','''')'
+ ' + ISNULL(Settlement + '', '','''') + ISNULL(Cou_Unit + '', '','''') + ISNULL(Postcode,'''')),'', , '', '', '')'
+ ' AS ClosestAddress FROM [UKStreetsAndPlaces].[dbo].[OS_Locator] ORDER BY '
+ ' (Longitude ' + @LongitudeOperator + ' ' + CAST(ABS(@Longitude) AS VARCHAR(20)) + ')'
+ ' * (Longitude ' + @LongitudeOperator + ' ' + CAST(ABS(@Longitude) AS VARCHAR(20)) + ')'
+ ' + (Latitude - ' + CAST(@Latitude AS VARCHAR(20)) + ') * (Latitude - ' + CAST(@Latitude AS VARCHAR(20)) + ') ASC'

Answer 2

You could do something like Replace all Comma with space and then do LTRIM and RTRIM and replace all space with comma.

Create table Data(name varchar(10),lastname varchar(10));

insert into Data values('','Doe');

insert into Data values('Jane','Doe');

insert into Data values('Jane','');

SELECT Replace(Rtrim(Ltrim(Replace(ISNULL(name,'') +',' + ISNULL(lastname,'') + ',',',',' '))),' ',',')
 from Data

something like : http://sqlfiddle.com/#!3/6a6c6/1

Answer 3

I don't know if you need dynamic SQL for some other reason, but I think something like this should work (with no Dynamic SQL); if you're really sure you need Dynamic SQL for some other reason, then just refactor this idea into your Dynanmic Statement:

DECLARE @ClosestAddress VARCHAR(1000)
SELECT TOP 1 
        @ClosestAddress = ISNULL(POI + ', ','')  
        + ISNULL(Name + ', ','')
        + ISNULL(Settlement + ', ','')
        + ISNULL(Cou_Unit + ', ', '')
        + ISNULL(Postcode,'')
        --AS ClosestAddress 
FROM [UKStreetsAndPlaces].[dbo].[OS_Locator] ORDER BY  (Longitude = 12.2132) * (Longitude = 12.2132) + (Latitude - 12.2132) * (Latitude - 12.2132) ASC

IF (RIGHT(@ClosestAddress, 2) = ', ')
    RETURN SUBSTRING(@ClosestAddress, 0, LEN(@ClosestAddress))
ELSE
    RETURN @ClosestAddress

Why this should work: Concatenating NULL + ', ' will result in an empty string. Then we check if the string ends with ', ', and if so we return everything but the last two characters.