Dynamic Sql Samples

There are a few dynamic sql examples here. I run my scripts, commands in AdventureWorks2017 database.

Sample-1

--Dynamic Sql Samples
use AdventureWorks2017
go

DECLARE @sqlcmd NVARCHAR(MAX);
SELECT @sqlcmd = ‘SELECT TOP 10 * FROM Person.Person’;
EXEC (@sqlcmd);

Beware: don’t use varchar type, always use nvarchar type while you use dynamic sql command. Because the command might contain extended unicode characters.

Dynamic Sql Execution Process generally is composed of 3 steps

1.Create a string variable that will store the Dynamic SQL.
2. Write a sql command and store it in this variable.
3. Execute the command string.

Sample-2

--Sample 2
DECLARE @sqlcmd1 NVARCHAR(MAX);
DECLARE @tblname NVARCHAR(100);
SELECT @tblname = ‘Person.Person’;
SELECT @sqlcmd1 = ‘SELECT TOP 10 * FROM ‘ + @tblname;
EXEC (@sqlcmd1);

In this sample I want to show dynamic sql for preparing backup scripts. I can put dynamic sql command in the path below. I use below script as a template in Sample-3.

BACKUP DATABASE [AdventureWorks2017]
TO DISK = ‘G:\database\backup\AdventureWorks2017.bak’
with compression;

Sample-3

use master
--fetch cursor sample
--It shows backup scripts
declare @db_list table(dbase_name sysname)
insert into @db_list
select [name] from sys.databases
where database_id>4  --- I don't want system databases' backup
declare @sqlcmd2 nvarchar(250)
declare @db_name sysname 
--What is sysname type? I will explain below

declare database_cursor cursor for
select [dbase_name] from @db_list
open database_cursor
fetch next from database_cursor into @db_name
while @@FETCH_STATUS = 0
begin
 SELECT @sqlcmd2 = ‘BACKUP DATABASE ‘ 
 + @db_name + ‘TO DISK=’’E:\SQLBackups\’ + @db_name + ‘.bak’’
 WITH COMPRESSION;’
PRINT (@sqlcmd2);
FETCH NEXT FROM database_cursor INTO @db_name;
end
close database_cursor
deallocate database_cursor

The result of Sample-3 is above. Instead of EXEC command I used PRINT command. Because I don’t want to perform backup.

What is sysname?

Note: The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. It is basically the same as using nvarchar(128) NOT NULL.

I have always used EXEC statement to perform dynamic sql
Unfortunately, EXEC comes with a number of limitations and security concerns because of this sp_executesql is more preferable

Why sp_executesql

1- EXEC is undefended to SQL injection and the effects of unexpected input.
Escape characters and apostrophes can easily wreck a Dynamic SQL statement.
2-There is no built-in way to manage input or output variables with EXEC.
3-When using EXEC, it is unlikely to reuse execution plans.This reuse of execution plans, known as parameter sniffing, is a useful feature and generally something you’ll want to occur.

Maybe another blog post, I might tell about dynamic sql and sql injection. But I don’t explain right now. Just show you basic usage of sp_executesql.

sp_executesql N’select top 10 * from Person.Person’

Sample-4

declare @cmd2 nvarchar(250)
set @cmd2=N’select top 10 * from Person.Person’
exec sp_executesql @cmd2

How to use dynamic sql in stored procs?

In this sample, I want to retrieve Person.Person table according to 3 filter.

Sample-5

CREATE OR ALTER PROCEDURE [dbo].[usp_SearchPerson] 
(
 @PersonID NVARCHAR(50) = NULL 
 ,@FirstName NVARCHAR(100) = NULL 
 ,@LastName NVARCHAR(100) = NULL
)
AS 
BEGIN 
 SET NOCOUNT ON; 
 
 DECLARE @SQL NVARCHAR(MAX)
 DECLARE @PersonIDFilter NVARCHAR(MAX)
 DECLARE @FirstNameFilter NVARCHAR(MAX)
 DECLARE @LastNameFilter NVARCHAR(MAX)
 
 
 set @PersonIDFilter=iif(@PersonID IS NULL OR @PersonID <= 0,’’+isnull(@PersonID,-999)+’=’+isnull(@PersonID,-999),’BusinessEntityID = ‘’’ + @PersonID + ‘’’’ )
 set @FirstNameFilter=iif(@FirstName IS NULL OR @FirstName = ‘’,’’+isnull(@FirstName,-999)+’=’+isnull(@FirstName,-999),’FirstName like ‘’%’ + @FirstName + ‘%’’’)
 set @LastNameFilter=iif(@LastName IS NULL OR @LastName = ‘’,’’+isnull(@LastName,-999)+’=’+isnull(@LastName,-999),’LastName like ‘’%’ + @LastName + ‘%’’’)
 
 SET @SQL = ‘SELECT BusinessEntityID
 ,FirstName
 ,MiddleName
 ,LastName
 ,PersonType
 ,Title
 FROM Person.Person
 WHERE ‘ + @PersonIDFilter
 + ‘ AND ‘ + @FirstNameFilter + ‘’
 + ‘ AND ‘ + @LastNameFilter + ‘’
PRINT (@sql)
 EXEC(@sql)
 
 
END

Note:CREATE OR ALTER statement is supported by SQL Server 2016 SP1 and upper versions.

Then execute the procedure.

exec [dbo].[usp_SearchPerson] @FirstName=’Ben’, @LastName=’Br’

If you pass no parameter it retrieves all data. Let’s try and see.

exec [dbo].[usp_SearchPerson]

Could we use dynamic sql with DDL and DCL command? Yes. We can create objects and authorise users etc…

Sample-6

create or alter proc spCreateView
(@tablename sysname,@loginname sysname=NULL)
as 
begin
declare @sql nvarchar(max) = N’use [AdventureWorks2017];
 
 exec (‘+’’’create view V’+@tablename+’ as select * from ‘+@tablename+’’’)’;
print (@sql);
if @loginname is not null
begin
 declare @dcl nvarchar(max) = 
 N’exec (‘+’’’grant select on V’+@tablename+’ to ‘+@loginname+’’’)’;
print (@dcl);
exec(@sql);
exec(@dcl);
end
end

This procedure create view dynamically by using @tablename parameter. And then if you put @loginname parameter, it authorises user to this view. Actually, I wrote this proc for a stackoverflow question. The user in stackoverflow wanted a proc that makes view with “select * from” should belong to all tables. And this is a part of my script solution.

Let’s create the script then execute it.

exec spCreateView ‘DatabaseLog’,’selcuk’

If I run the sys.views system view, I see the view in the database.

select * from sys.views where name =’VDatabaseLog’

Also, If look at the user properties, you can see the authorising completed.

Yes, dynamic sql with dcl ran correct.