Remove the first word in my sql statement

Question

I have a function in Microsoft SQL that creates auto sql lines, a (Querybuilder). In this case I have this line in one of my Cursors That looks something like this:

  set @Query = @Query + ' AND ('+ (case when(@CountryDelivery = 1) then 'Delivery' else     'pickup' end) +'.CountryUK '+ (case(@CountryExcl) when 1 then ' <> ''' else ' = ''' end) + @country + ''')'    

This will make my result look something like: "AND (country = 'USA') AND (Country = 'Canada') ......"ETC, all depends how many countries the user stores in his datatable.

My challenge is that I want the "AND" statement to be removed at the start of the Query but not if the user add more countries.

This is my full function:

SET @Query = @Query + ' ('  
declare Accountring_Country1Cursor Cursor for 
        select Country, Exclude, IsDelivery, ID from dbo.Accounting_Country1 where Service_ID = @Service_ID        
OPEN Accountring_Country1Cursor
FETCH NEXT FROM Accountring_Country1Cursor 
INTO @country, @CountryExcl, @CountryDelivery, @country_ID
WHILE @@FETCH_STATUS = 0
BEGIN
set @Query = @Query + ' AND ('+ (case when(@CountryDelivery = 1) then 'Delivery'     else 'pickup' end) +'.CountryUK '+ (case(@CountryExcl) when 1 then ' <> ''' else ' = ''' end) + @country + ''')'    
    FETCH NEXT FROM Accountring_Country1Cursor 
INTO @country, @CountryExcl, @CountryDelivery,@country_ID
END 
CLOSE Accountring_Country1Cursor
DEALLOCATE Accountring_Country1Cursor 
SET @Query = @Query + ') '              
RETURN @Query

     END

Answer 1

Try this:

declare @filter varchar(max);
set @filter = '(1 = 1)';

declare Accountring_Country1Cursor Cursor for select Country, Exclude, IsDelivery, ID from dbo.Accounting_Country1 where Service_ID = @Service_ID        
OPEN Accountring_Country1Cursor

FETCH NEXT FROM Accountring_Country1Cursor INTO @country, @CountryExcl, @CountryDelivery, @country_ID
WHILE @@FETCH_STATUS = 0
BEGIN
    set @filter = @filter + ' AND ('+ (case when(@CountryDelivery = 1) then 'Delivery'     else 'pickup' end) +'.CountryUK '+ (case(@CountryExcl) when 1 then ' <> ''' else ' = ''' end) + @country + ''')'    
    FETCH NEXT FROM Accountring_Country1Cursor INTO @country, @CountryExcl, @CountryDelivery,@country_ID
END 

CLOSE Accountring_Country1Cursor
DEALLOCATE Accountring_Country1Cursor 

SET @Query = @Query + '(' + @filter + ') '              
RETURN @Query

Basically, the default value for the filter is true. Then you can always add a new ' AND (something)', because it will be valid SQL. And after you're done building the filter, you just append it to your @Query variable, all at once.

Or, in a more straightforward way, you can just take a substring of your final query:

set @Query = '(' + substring(@Query, 4, len(@Query))

Or, slightly more complicated but more versatile:

set @Query = substring(@Query, 0, 1) + substring(@Query, 4, len(@Query))