How to Build SQL Server Dynamic SQL Query Example
In this SQL tutorial, sql developers will find an sql dynamic query example.
Creating a T-SQL dynamic query is easy using string concatenation.
I will try to build a dynamic sql query in SQL Server which can be altered and placed in a SQL Server stored procedure.
declare @sql nvarchar(max)
declare @where nvarchar(10) = '
where '
declare @and nvarchar(10) = ''
declare @name nvarchar(50) = N'ML'
declare @finishedgood bit = 0
declare @class nchar(2) = N'M'
declare @safetystock smallint = 1000
SET @sql = N'
select *
from Production.Product'
if @name is not null
begin
set @sql = @sql + @where + @and + 'Name LIKE N''' + @name + '%'''
set @where = ''
end
if @finishedgood is not null
begin
if @where = '' set @and = ' and
'
set @sql = @sql + @where + @and + 'FinishedGoodsFlag = ' + CAST(@finishedgood as nvarchar(3))
set @where = ''
end
if @class is not null
begin
if @where = '' set @and = ' and
'
set @sql = @sql + @where + @and + 'Class = N''' + @class + ''''
set @where = ''
end
if @safetystock is not null
begin
if @where = '' set @and = ' and
'
set @sql = @sql + @where + @and + 'SafetyStockLevel <= ' + CAST(@safetystock as nvarchar(10))
set @where = ''
end
print @sql
exec sp_executesql @sql
The above dynamic sql query forms the below sql Select statement.
select *
from Production.Product
where Name LIKE N'ML%' and
FinishedGoodsFlag = 0 and
Class = N'M ' and
SafetyStockLevel <= 1000
After creating dynamic sql query with concatenate string method, in order to get a result set sql developers can run the final t-sql script using sp_executesql procedure.
And the returned sp_executesql result set of the above dynamic sql query is as follows :
