Dynamic SQL query for where in clause

For multiple criteria conditions

2 min readSep 23, 2021

Photo from: https://www.newworldai.com/sql-tutorial/

Suppose we have country name and country code mapping table as below:

We would like to provide a service that accept multiple country code as input and return the correspond country information back.

For instance, if we have Country Code [297, 355, 1–268] for the criteria and we would expect [Aruba, Albania, Antigua and Barbuda] return.

For far we suppose that mapping table all keep inside the table of database: [dbo].[Country] and we plan to retrieve relative country data through below store procedure:

create procedure [dbo].[Usp_Read_Country_Code_Mapping]
      @Codes varchar(max) = null
      , @Delimiter char(1) = ','
as
begin
      set nocount on;
      declare @Sql nvarchar(5000);
      set @Sql = N'     
      select 
         [IDX]
        ,[CountryCode]
        ,[CountryName]
        ,[CreateUser]
        ,[CreateDate]
        ,[UpdateUser]
        ,[UpdateDate]
      from [dbo].[Country]
      where [CountryCode] 
      in ( select value from STRING_SPLIT( @Datas, @Token )) ';     if @Codes = '' or @Code is null
     begin
          -- query for all records without any condition
          set @Sql = @Sql + ' or 1=1 ';  
     end;
     exec [dbo].[sp_executesql] @Sql, N'@Datas varchar(max), @Token char(1)', @Codes, @Delimiter;   if @@ERROR <> 0
      begin
        select 
                  ERROR_NUMBER() AS [ErrorNumber]
                , ERROR_SEVERITY() AS [ErrorSeverity]
                , ERROR_STATE() AS [ErrorState]
                , ERROR_PROCEDURE() AS [ErrorProcedure]
                , ERROR_LINE() AS [ErrorLine]
                , ERROR_MESSAGE() AS [ErrorMessage];
    end;   return 0;
end;
go

The key point1 here was we introduce dynamic SQL for combined parameters into SQL statement.
The point2 was that we used built-in function “STRING_SPLIT”. The function will split the list of string data to table structure according specific token.

Please note that “STRING_SPLIT” only support at least of database compatibility level that ≥130.

If your SQL server compatibility level were under 130 then you will see below message:

Here was the SQL server version for its compatibility level:

Or you could check your database compatibility level through below script:

select compatibility_level
from sys.databases where name = 'Your_Database_Name';
go

Dynamic SQL query for where in clause

For multiple criteria conditions

Reference

Written by LAI TOCA

No responses yet