SQL:Dynamic SQL

Refer the Source Video:Dynamic SQL

*****************************************************************************************************************************************************************

Dynamic SQL Basics

*****************************************************************************************************************************************************************

***********************
What is Dynamic SQL?
***********************

1.Sql statement when one convert into a text by using ' ' and then executing it,thats Dynamic SQL.

--Using Dynamic SQL

SELECT * FROM tblFilm

EXECUTE ('SELECT * FROM tblFilm')

--can use  EXEC ('SELECT * FROM tblFilm')

******************************
Using System Stored Procedure:
******************************

--Using Dynamic SQL

EXEC sp_executesql N'SELECT * FROM tblFilm'

Note:
1.Uniquecode string , data in nVarchar N'SELECT * FROM tblFilm.
2.sp_executesql - interpret uniquecode strings.


******************************
Which Method Should You Use?
******************************

note:queryplan:store in cache:EXEC ,it executes the query every time.

using stored procedure "sp_executesql" keeps the plan,so its faster.


******************************
Concatenating a Dynamic SQL String:
******************************
--Concatenating a Dynamic SQL String

DECLARE @Tablename NVARCHAR(128)
DECLARE @SQLString NVARCHAR(MAX)

SET @Tablename = N'tblFilm'
SET @SQLString = N'SELECT * FROM'+ @Tablename

EXEC sp_executesql @SQLString

******************************
Concatenating with Numbers:
******************************
--Concatenating numbers

DECLARE @Number INT
DECLARE @NumberString NVARCHAR(4)
DECLARE @SQLString NVARCHAR(MAX)

SET @Number = 10
SET @NumberString = CAST(@Number AS NVARCHAR(4))

--N'SELECT TOP 10 * FROM tblFilm ORDER BY FilmReleaseDate'

--SET N'SELECT TOP + ' + @Number + ' * FROM tblFilm ORDER BY FilmReleaseDate'

--(with the above,it would give error,Conversion failed when converting the nvarchar value SELECT TOP 10' to data type int
--what it does is try to find 'SELECT TOP ' as a number and to add to @Number
--what we need is to convert @Number to a string, using cast,convert function

SET N'SELECT TOP + ' + @NumberString + ' * FROM tblFilm ORDER BY FilmReleaseDate'


EXEC sp_executesql @SQLString


*************************************************************************************************************************************************************

Creating Stored Procedures

*****************************************************************************************************************************************************************

--Creating stored procedures


CREATE PROC spVariableTable ( @TableName NVARCHAR(128) )

AS
BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = N'SELECT * FROM' + @TableName

EXEC sp_executesql @SQLString 
END


--
EXEC spVariableTable 'tbleFilms'

EXEC spVariableTable 'tbleAccts'

--

***********************
Adding Multiple Parameters:
***********************

ALTER PROC spVariableTable ( @TableName NVARCHAR(128),@Number INT )
...Refer Video
DECLARE @NumberString NVARCHAR(4)
SET @NumberString =CAST(@Number AS NVARCHAR(4))
SET @SQLString = N'SELECT * TOP' + @NumberString  + '* FROM' + @TableName
...Refer Video


EXEC spVariableTable 'tbleAccts',15


***********************
Using the IN Operator:
***********************

--Dynamic SQL and the IN Operator

cCREATE PROC spFilmYears( @YearList NVARCHAR(MAX)
AS
BEGIN

DECLARE @SQLString NVARCHAR(MAX)

  SET @SQLString =

N'SELECT * FROM tblFilm WHERE YEAR(FilmReleaseDate) IN(' + @YearList + N') ORDER BY FilmReleaseDate'

 EXEC sp_executesql @SQLString
END


---
EXEC spFilmYears '2000,2001,2002'
---

***********************
Parameters of sp_executesql:
***********************
--Parameters of sp_executesql

EXEC sp_executesql N'SELECT FileName,FilmreleaseDate From tblFilm WHERE  FilmRunTimeMinute > @Length'
AND FilmReleaseDate > @StartDate,
N'Length INT',
@StartDate,
@Length = 180,
@StartDate ='2001-01-01'

***********************
The Danger of SQL Injection:
***********************

--SQL Injection

EXEC spVariableTable 'tblFilm'


EXEC spVariableTable 'tblFilm; DROP TABLE tblTest'

( it will drop table)

The Curse and Blessings of SQl Injection attack.

Search: