Dynamic SQL query for where in clause

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 * 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

Reference

Coding for fun

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Learning to Code, and Why Nerds Need to Stop Teaching It

I started a TikTok!

Hello Flutter

Google Code Jam 2022- Qualification Round

Cisco ACI: APIC Certificate Bug (it’s a nasty time-waster)

Zuri Internship Goals — Https://internship.zuri.team

An easy way to install a docker SWARM cluster with ANSIBLE ?

Connect to the Exact Online API with Postman

EOL API Call Refresh Token in Postman

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
LAI TOCA

LAI TOCA

Coding for fun

More from Medium

SQL Server Primary Keys

SQL Server Procedure Multi Statement as Bit Wisely

SELECT statement for SQL

SELECT statement for SQL