|
|
|
|
|
|
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 :
|
Related SQL Resources
SQL Server Articles
SQL Server 2012
SQL Server Tools
SQL Blog
SQL Server 2008 Blog
Certification Exams Blog
Reporting Services Blog
Analysis Services Blog
MS SQL Server Forums
Free Exam Vouchers
|
|
|
|
|
|
|
|
|