Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday 25 April 2014

Executing a TSQL batch multiple times using GO:

Executing a TSQL batch multiple times using GO:

Ref:

Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing.  Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.
SolutionIn both SQL Server 2000 and SQL Server 2005 the keyword GO tells SQL Server to execute the preceding code as one batch.  In SQL Server 2005 you have the ability to add a number after the GO command to tell SQL Server how many times to execute the batch.  So let's take a look at a couple of examples:
Let's say you want to create a test table and load it with 1000 records.  You could issue the following command and it will run the same command 1000 times:
CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier) GO
INSERT INTO dbo.TEST (ROWIDVALUES (NEWID())  GO 1000
Here is another example that executes both INSERT statements 1000 times.   As you can see you can add more and more statements to the batch to be run the set number of times that is specified after the GO.
CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifierCREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID uniqueidentifierGO 
INSERT INTO dbo.TEST (ROWIDVALUES (NEWID())  INSERT INTO dbo.TEST2 (ROWIDVALUES (NEWID())  GO 1000
To do something similar to this in SQL Server 2000 you would need to write code such as the following. It is not that big a deal, but writing GO 1000 seems a bit easier to me.

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier) CREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID uniqueidentifier) GO

DECLARE @counter INT 
SET 
@counter 
WHILE @counter 1000 
  BEGIN
       INSERT INTO 
dbo.TEST (ROWIDVALUES (NEWID())
       
INSERT INTO dbo.TEST2 (ROWIDVALUES (NEWID())
  
SET @counter @counter 1
  END

Rename Database in SQL Server While Database in Use

Rename DB in SQL Server While Database in Use

Option 1 - Rename SQL Server Database using T-SQL

This command works for SQL Server 2005, 2008, 2008R2 and 2012:
ALTER DATABASE oldName MODIFY NAME = newName
If you are using SQL Server 2000 you can use this T-SQL command to make the database name change.  This still works for SQL 2005, 2008, 2008R2 and 2012, but Microsoft says it will be phased out at some time.
EXEC sp_renamedb 'oldName', 'newName'

Option 2 - Rename SQL Database using SSMS

If you are using SQL Server Management Studio, right click on the database name and select the option "Rename". This did not exist in SQL Server 2000, but if you use Management Studio to manage SQL Server 2000 you can also take advantage of this option.
rename database using ssms

Option 3 - Rename SQL database using detach and attach

Use the detach and attach feature of SQL Server to detach the database first and when you reattach the database you give the database a different name. This can be done by using the SSMS or you can do this by using the following commands:
EXEC sp_detach_db 'oldName', 'true'

EXEC sp_attach_db @dbname = N'newName', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
Here we are detaching database "Test"
detach database using ssms
Here we are reattaching database "Test", but before we finish we change the name of the database to "Test2".
attach database using ssms
One thing to note is by changing the name of the database using one of these techniques you are only renaming the database.  The physical files still have the same names, so if you want to also change the name of the files the simplest approach is to use Option 3.  Before you reattach the files you need to first change the name of the physical files and then when you do the reattach you can specify the renamed files.

Option 4

ReName DB in put cursor on Database see below:




But when Database in Use or SQL Query window is in opened state or Any application using Database time
You cant able to replace database.

So use this below script to kill/close entire connection of db and renaming db


----------------------DB Rename script------------------


USE master
GO
ALTER DATABASE Demodatabase 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO

--ALTER DATABASE oldName MODIFY NAME = newName

ALTER DATABASE Demodatabase MODIFY NAME=DemoDB
GO

ALTER DATABASE DemoDB 
SET MULTI_USER 
GO

----------------------------------------------------------------





Above steps only rename dbname i.e logical name and it did not replace physical name or file path name

(i.e)

One thing to note is by changing the name of the database using one of these techniques you are only renaming the database.  The physical files still have the same names, so if you want to also change the name of the files the simplest approach is to use Option 3.  Before you reattach the files you need to first change the name of the physical files and then when you do the reattach you can specify the renamed files.
Next Steps

  • In addition to changing the names of the databases you also need to check to see if there are any references in your application code to the database name.  This can be either within SQL Server or outside of SQL Server.  By making this name change, nothing else is changed so you may need to do a lot of additional work to change a database name.
  • See if there are databases that could use a name change and plan the change process to take advantage of a more meaningful database name.
Best Practice for renaming a SQL Server Database/ Rename database also with physical name:

Ref: 

ProblemOne of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.
SolutionDatabase Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files.
It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.
In this tip, you will see the steps which you need to follow to rename a SQL Server Database using the ALTER DATABASE command.

Creating a Sample Database Namely CoreDB
Let's first create a new database named CoreDB using the T-SQL below:
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB] 
ON PRIMARY 
( 
NAME = N'CoreDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' , 
SIZE = 2048KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'CoreDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO
Rename CoreDB Database Using sp_renamedb System Stored Procedure
Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.
USE master
GO
ALTER DATABASE CoreDB 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB 
SET MULTI_USER 
GO
Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:
USE master
GO
/* Identify Database File Names */
SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO
Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database
DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.
This first set of commands put the database in single user mode and also modifies the logical names.
/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO
This is the output from the above code.

Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.
/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO
Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature using the sp_configure system stored procedure.
USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.
/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO
Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:
/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO
Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:
/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER 
GO
You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:
USE master
GO
/* Identify Database File Names */
SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO

You can see in the above snippet that the Logical Name and File Name for ProductsDB are now correct.




Friday 11 April 2014

Script To Check Last Full Backup Duration of All Database

Script To Check Last Full Backup Duration of All Database:

set nocount on
go
if exists ( select name from tempdb..sysobjects where name like '#DatabasesBackupsDuration%')
drop table #DatabasesBackupsDuration
go
create table #DatabasesBackupsDuration
(
ServerName varchar(100) null,
DBName varchar(100) null,
RecoveryModel varchar(100) null,
LastFullbackup datetime null,
FullbackupDurationSec bigint null,
DBStatus varchar (100) null,

)
go
insert into #DatabasesBackupsDuration(ServerName,DBName)
select convert(varchar,serverproperty('ServerName')),a.name 
from master.dbo.sysdatabases a
where a.name <> 'tempdb'

update #DatabasesBackupsDuration
set LastFullbackup=b.backup_start_date
from #DatabasesBackupsDuration a,(select database_name,max(backup_start_date) backup_start_date 
from msdb..backupset  where type='D' group by database_name)b
where a.DBName=b.database_name

update #DatabasesBackupsDuration 
set RecoveryModel=convert(sysname,DatabasePropertyEx(DBName,'Recovery'))

update #DatabasesBackupsDuration 
set DBStatus=convert(sysname,DatabasePropertyEx(DBName,'Status'))

update d
set d.FullbackupDurationSec = datediff(s,backup_start_date, backup_finish_date)
from #DatabasesBackupsDuration d,(select database_name, max(backup_start_date) as backup_start_date, max(backup_finish_date) as backup_finish_date from msdb..backupset 
where type ='D' group by database_name) b where d.DBName = b.database_name
go
select * from #DatabasesBackupsDuration order by LastFullbackup
go
select CAST(SUM(FullbackupDurationSec)/60 AS varchar(100))+' Minutes' As FullBackupTimeTotal from #DatabasesBackupsDuration
go
drop table #DatabasesBackupsDuration



Wednesday 9 April 2014

SQL Server Queries taking long time to Execute in Production:

SQL Server Queries taking long time to Execute in Production:

Query Execution in the production taking long time

one of the query in the production taking almost 40 minutes instead seconds where it used to, but interestingly the same query in the development server just took 5 seconds to execute. both the environments are same WRT user contention.
The quick steps to troubleshoot in this scenario is
  1. compare the pre-execution plans (Ctrl + L) on both the production and development.

    Note : pre-execution plans are helpful to look at the execution plan with out executing the query as executing query in production with out knowing about is not recommended in production.

    Just look at both the plans on whether they are look same or not with out going in details
    if both are not same then its due to the statistics are not same.
  2. so just check when the statistics in production are updated using the below query

    SELECT STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats
    WHERE object_id = OBJECT_ID(Sales.Customer);
      -- to check the statistics on indexes , instead sys.stats use sys.indexes

    SELECT STATS_DATE(object_id, index_id) AS statistics_update_date
    FROM sys.indexes WHERE object_id = OBJECT_ID(Sales.Customer);
  3. if these dates are too old then update the statistics using the below command
    UPDATE STATISTICS Person.Address
    check the performance of the query in production .
Note: even if the “auto create statistics” and “auto update statistics are on” , SQL Server wont create or update for each and every row modification . it will only do when it reaches certain threshold.

Monday 7 April 2014

Introduction of Dynamic SQL

Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1):
Introduction to Dynamic SQL (Part 1)

Introduction to Dynamic SQL (Part 1)
Ref:
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2



Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.
Dynamic SQL on the client
If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = "
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.
Dynamic SQL in a stored procedure
Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
        @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
        Articles
WHERE
        ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
        @TableName VarChar(100)
AS
SELECT *
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
        @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.



Introduction to Dynamic SQL (Part 2):

Welcome to Part 2 of my Introduction to Dynamic SQL.
In part one I explained what Dynamic SQL is and how to use it. In this article I will show some more useful applications for it and a few tricks.
The IN Clause
The IN clause is a good example of a use for Dynamic SQL. A lot of SQL Server developers use ASP or a similar web scripting language.
If in an asp page you have a Select list with multiple allowed values, the value of request.form("myList") on the processing page might look like this "1,3,4,6".
So we try to write a stored proc around this
Create Procedure Search
        @strIDs VarChar(100)
AS

SELECT *
FROM
        Products
WHERE
        ProductID in (@strIDs)

GO
Oooops! No Go.
This will work
Create Procedure Search
        @strIDs VarChar(100)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')'

Exec ( @SQL)

GO
N.B. This can also be solved using a technique like this.
Aliases
Giving a table or column a dynamic alias is a use for dynamic SQL.
This will not work
Select UserName FROM Table as @Alias
This will
Exec('Select UserName FROM Table as ' @Alias)
DDL
A common question asked of SQL Team is "How do I write a stored procedure that will create a table/database. I want to pass in the name"
SQL Server will not allow this
Create Table @TableName (
        ID int NOT NULL Primary Key,
        FieldName VarChar(10)
        )
Once again, dynamic SQL to the rescue
Declare @SQL VarChar(1000)

SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)
Similarly, the code to create a database would look like this:
Exec('Create Database ' + @myDBName)
sp_executesql
sp_executesql is a system stored procedure that you can use in place of "exec" to execute your dynamic sql.
This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.
An example
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'

Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
The first parameter here is the SQL statement, then you must declare the parameters, after that you pass the in parameters as normal, comma separated.
sp_executesql is also useful when you want to execute code in another database as it will run code in the context of it's database, rather than the one it was called from.
Try this from a database that is not Pubs
Create View pubs.dbo.Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)
You will get this error: 'CREATE VIEW' does not allow specifying the database name as a prefix to the object name.
So you build the dynamic sql, then run it in Pub's copy of sp_executesql
I.E.
Declare @SQL nVarChar(1000)

Select @SQL = 'Create View Auths AS (SELECT au_id, au_lname, au_fname FROM Authors)'

Execute pubs.dbo.sp_executesql @sql
Permissions
When executing dynamic SQL from a stored procedure, keep in mind that the SQL is executed in the permission context of the user, not the calling procedure. This means that if your user has no rights to the tables, only to the procedure, you may run into problems.
Scope
When you run dynamic sql, it runs in it's own scope.
This
exec('set rowcount 3')

Select * from Authors

exec('set rowcount 0')
Will have no effect on the result set returned from Authors. This is because by the rowcount statements have gone out of scope by the time the Select occurs.
This would be solved by this
exec('set rowcount 3 Select * from Authors Set rowcount 0')
Declaring variables inside a dynamic SQL batch will also not be available outside the batch and vice versa. As a result, this would also not work.
declare @i int
Exec ('Select @i = 1')
Temp tables can be used to interact between batches of standard SQL and dynamic SQL. A temp table created within a dynamic SQL batch will be destroyed when the batch completes, however a temp table created before the batch will be available to it.
Create Table #tempauth(
        au_id VarChar(100),
        au_fname VarChar(100),
        au_lname VarChar(100)

)

declare @SQL VarChar(1000)
Select @SQL = 'Insert into #tempauth Select au_id, au_fname, au_lname FROM Authors'
exec(@SQL)

Select * from #tempauth

Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " 
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
 @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
 Articles
WHERE
 ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS
SELECT * 
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpu
Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed. As a result it is a very flexable and powerful tool. You can use dynamic sql to accomplish tasks such as adding where clauses to a search based on what fields are filled out on a form or to create tables with varying names.
In part one of this two part series I will introduce you to dynamic SQL and give you some simple examples. In part two I will explain some more advanced uses for it and answer a lot of the questions we get in the forums.

Dynamic SQL on the client

If you are an ASP developer you would be already familiar with the concept of dynamic SQL. How may times have you done something like this:
dim sql
sql = "Select ArticleTitle, ArticleBody FROM Articles WHERE ArticleID = " 
sql = sql & request.querystring("ArticleID")

set results = objConn.execute(sql)
or slightly more elaborate
dim sql
sql = "Insert into Users (UserName, FirstName, LastName, EMailAddress) "
sql = sql & "Values('" & request.form("UserName") & "', '" & request.form("FirstName")
sql = sql & "', '" & request.form("LastName") & "', '" & request.form("EmailAddress") & "')"

objConn.execute(sql)
Or for a generic table viewer
dim sql
sql = "Select * from " & request.querystring("TableName")
set results = objConn.execute(sql)
In each case, you are building your sql statement as a string, then executing that statement against an active database connection.

Dynamic SQL in a stored procedure

Once you move into the realm of stored procedures, you move away from this style of coding. Instead you would create a procedure with an input parameter.
Create Procedure GetArticle
 @ArticleID int
AS

Select ArticleTitle, ArticleBody
FROM
 Articles
WHERE
 ArticleID = @ArticleID

GO
However, SQL Server doesn't like certain things being passed as parameters, object names are a good example. If you try the third example in a stored proc such as:
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS
SELECT * 
FROM @TableName

GO
You will get an error. To get around such restrictions we can use dynamic SQL. We will follow the same logic here, build a string, then execute it.
Create Procedure GenericTableSelect
 @TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
Try that. That should do it.
The downside of this method is twofold. Firstly, and most importantly, your stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.
The other downside, IMHO, is that you lose the nice formating you are able to achieve with stored procedures that you were not able to do when you were building queries in ASP.
The advantage is, of course, that you are able to achive a flexability in your code that you can not get with standard SQL.
That wraps up part one. Hopefully you now have an idea of what dynamic SQL is and why you would want to use it. In part two I will demonstrate some more complex and real world examples as well as some techniques for caching and speeding up dynamic queries.
Until then have fun.
- See more at: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1#sthash.AOMhcydS.dpuf
drop table #tempauth
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)
Introduction to Dynamic SQL (Part 1)