Advertisements

Archive

Posts Tagged ‘Dynamic SQL’

Dynamic SQL usage, when to use? – MSDN TSQL forum

March 4, 2015 Leave a comment

–> Question:

Can someone tell when or where to use Dynamic SQL ( exec sp_executesql and exec commands) over normal SQL?
 

–> My Answer:

Dynamic SQL queries should be avoided and one should put more thought and time on creating non-dynamic SQL statements.

But there could be some rare scenarios or requirements where you need to create Dynamic SQL, like doing some multiple DBA activities in one go, like:

– Enabling/Disabling multiple Jobs at once, link.

– Creating Dynamic PIVOT where there could be multiple columns based on multiple rows, link.

– Use sp_executeSQL instead of EXEC (SQL statement) to avoid SQL-Injection.

– Check this article by Erland on Dynamic SQL.

– Check this blog post for EXEC (SQL statement) vs sp_executeSQL.
 

Ref Link.


Advertisements

EXECUTE (or EXEC) vs sp_executesql

March 22, 2011 6 comments

–> EXECUTE:
As per MS BOL EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.

–> sp_executesql:
As per MS BOL sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.

–> Main difference performance wise: sp_executesql is generally preferred over EXEC() when executing dynamic T-SQL. sp_executesql works by creating a stored procedure using the specified query, then calling it using the supplied parameters. Unlike EXEC(), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and encourage plan reuse. A dynamic query that is executed using sp_executesql has a much better chance of avoiding unnecessary compilation and resource costs than one ran using EXEC().

Let’s check the difference with a simple example using [AdventureWorks] database’s [Person].[Contact] table:

USE [AdventureWorks]
GO

DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

SET @fName = N'James'
SET @ePromo = 1

--// Using EXECUTE or EXEC
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = ''' + @fName + '''
AND EmailPromotion = ' + CAST(@ePromo as NVARCHAR(20))

PRINT @str

EXEC (@str)
GO

--// Using sp_executesql
DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

DECLARE @paramList NVARCHAR(500)
SET @paramList = N'@fNameParam NVARCHAR(20), @ePromoParam INT'

SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = @fNameParam
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 1
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo

-- When the match is LIKE
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName LIKE ''%'' + @fNameParam + ''%''
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 0
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo
GO

Security Note:
Before you call EXECUTE or sp_executesql with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection.

Creating “Dynamic PIVOT” scripts in SQL Server

January 25, 2011 24 comments

My previous PIVOT UNPIVOT post describes to organize data in a cross tab fashion or to transpose columns into rows and vice-versa.

This is fine only when you know how many columns you need. But when columns vary according to the query then how could you apply them on your scripts. The only way is to store them in a string variable at runtime and apply them in a dynamic SQL query, shown below.

This problem was also discussed on MSDN’s following link: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/26f86fd6-6d06-4cc5-9723-9ee4685bd48a where I proposed an answer also described below:

USE [tempdb]
GO

-- Create test tables
create table table1 (number int, desc varchar(20),
location int, numberatlocation int)
create table table2 (code int, name varchar(20))

-- Insert test data
insert into table1 values (12345,'test',1000,5)
insert into table1 values (12345,'test',1001,2)
insert into table1 values (12345,'test',1002,4)
insert into table1 values (12345,'test',1003,9)
insert into table1 values (12345,'test',1004,7)

insert into table2 values (1000,'loc1')
insert into table2 values (1001,'loc2')
insert into table2 values (1002,'loc3')
insert into table2 values (1003,'loc4')
insert into table2 values (1004,'loc5')

-- Static PIVOT
select number, description, [loc1], [loc2], [loc3], [loc4], [loc5]
from (select number, desc, numberatlocation, name
from table1 join table2 on table1.location=table2.code)p
PIVOT(MAX (numberatlocation) FOR Name IN ( [loc1], [loc2], [loc3], [loc4], [loc5] )
) AS pvt
ORDER BY number
Output of Static query:
number	desc	loc1	loc2	loc3	loc4	loc5
12345	test    5       2       4       9       7
-- Dynamic PIVOT

-- Lets add one more record on both the tables to check the results
insert into table1 values (12345,'test',1005,3)
insert into table2 values (1005,'loc6')

declare @col varchar(1000)
declare @sql varchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(name)
from table2

select @col -- This gives: [loc1], [loc2], [loc3], [loc4], [loc5], [loc6]

-- Now setting this @col variable in the Dynamic SQL.
set @sql = '
select number, desc, ' + @col + '
from (select number, desc, numberatlocation, name
from table1 join table2 on table1.location=table2.code)p
PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )
) AS pvt
ORDER BY number'

print @sql

exec (@sql)
Output of Dynamic query:
number	desc	loc1	loc2	loc3	loc4	loc5    loc6
12345	test    5       2       4       9       7       3
-- Final Cleanup
drop table table1
drop table table2
Categories: SQL Tips Tags: , ,