SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

Install SQL Server 2019
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

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
 set @sql = @sql + @where + @and + 'Name LIKE N''' + @name + '%'''
 set @where = ''

if @finishedgood is not null
 if @where = '' set @and = ' and
 set @sql = @sql + @where + @and + 'FinishedGoodsFlag = ' + CAST(@finishedgood as nvarchar(3))
 set @where = ''

if @class is not null
 if @where = '' set @and = ' and
 set @sql = @sql + @where + @and + 'Class = N''' + @class + ''''
 set @where = ''

if @safetystock is not null
 if @where = '' set @and = ' and
 set @sql = @sql + @where + @and + 'SafetyStockLevel <= ' + CAST(@safetystock as nvarchar(10))
 set @where = ''

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 :

Create dynamic sql select statement

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems