1.But at last when we should or should not use dynamic SQL ?
Well, we should not always prefer what is
dynamic to what is static by several
reasons. With regard to databases, where possible
we should use static instructions. The
static instructions are those that
we usually program in an application and that will be
forwarded to the database by an access method.
They can be static to the database and be
dynamically mounted in the application.
When to use :
1º Dynamic SQL is useful when you are replacing parts of a query that cannot be replaced by other methods.
2º SQL whose text is unknown at compile time.
3º SQL that is not supported as static SQL.
When not using :
1º For security, the dynamic SQL is propitious to occur risks if you do not know how to deal with it correctly.
2º The cache and plan optimization using applied parameterization to help deal with constant recompilations.
3rd Successful build creates scheman object dependencies.
An example of dynamic SQL :
Nondynamic sql command :
var command = new SqlCommand("select * from myTable where id = @someId");
command.Parameters.Add(new SqlParameter("@someId", idValue));
Dynamic sql command :
var command = new SqlCommand("select * from myTable where id = " + idValue);
What care should we take when using ?
You need to be very careful with dynamic SQL. A very dynamic SQL can drag down the performance of SQL Server because it cannot store optimization details or dynamically generated SQL execution plans. Blocking problems may occur as well.
If we wish to make some manipulation
the data returned by a dynamic execution,
we should make use of a temporary table that will be
completed with return to execution. This table
temporary will store the last result of(s)
dynamic instruction(s) (s) and shall contain the
same amount and data types of the columns as the result.
The dynamic instructions
are executed in the context of the current database and,
if within the dynamic instruction the context
database is modified, it is only valid
during the dynamic execution.
3. What are the restrictions ?
A common question among dynamic SQL users is why the following code does not work :
CREATE PROCEDURE my_proc @tablename sysname AS
SELECT * FROM @tablename
When you start passing table names and columns as parameters, you are definitely losing control.
If you want to do the above (except the fact that SELECT * should not be used in the production code), to reduce typing, you are on the wrong track. It is much better to write ten or twenty stored procedures, even if they are similar to each other.
UPDATE tbl SET @colname = @value WHERE keycol = @keyval
In this case, people want to update a column they want to select at runtime. The above description is really cool in T-SQL, but what happens is simply that the @colname variable is assigned to the @value value value value for each affected row in the table.
In this case, dynamic SQL would ask the user to have UPDATE permissions in the table, something that is not good. Therefore, there is every reason to avoid it. Here is a fairly simple solution:
UPDATE tbl
SET col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END,
col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END,
...
Another mistake that people have in common is wanting to access another database through the following way in dynamic SQL :
SELECT * FROM @dbname + '..tbl'
In this case, the table is in another database, which is somehow dynamically determined. There seem to be several reasons why people want to do this, and depending on their underlying reason, the solution is different.
Another common mistake is that people create tables with unknown columns :
CREATE TABLE with Unknown Columns
Sometimes I see people breaking their heads to understand why they create a temporary table in dynamic SQL, and then they can’t access it, because it disappeared when dynamic SQL came out. What you need to do is create the table outside of dynamic SQL.
One solution is to create a global temporary table, one with two # in the name, for instance of ##. This table is visible to all processes (so you may have to take precautions to make the name unique), and unless you explicitly leave it, it exists until your process exits.
4.What are the alternatives ?
I see a lot of people complaining that dynamic SQL is not safe, and it might not be safe even if you don’t know how to handle it correctly,because of SQL Injection it is very dangerous for you to handle important data if you don’t know about dynamic SQL.
Example of SQL Injection :
DROP TABLE secret_records;
CREATE TABLE secret_records (
user_name VARCHAR2(9),
service_type VARCHAR2(12),
value VARCHAR2(30),
date_created DATE
);
INSERT INTO secret_records (
user_name, service_type, value, date_created
)
VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete', SYSDATE);
INSERT INTO secret_records (
user_name, service_type, value, date_created
)
VALUES ('Chuck', 'Merger', 'Buy company XYZ', SYSDATE);
There are some alternatives to dynamic SQL,some of them are :
sp_executesql
It is a cleaner way to avoid concatenating the database name for each object referenced in the declaration.
USE TOOLS;
GO
ALTER PROCEDURE getTableNames @db_name sysname
AS
BEGIN
-- use a @sql variable to store the whole query
-- without concatenating the database name
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT name FROM sys.tables';
-- concatenate the database name to the
-- sp_executesql call, just once
DECLARE @cmd nvarchar(max);
SET @cmd = 'EXEC '+ QUOTENAME(@db_name) +'.sys.sp_executesql @sql';
EXEC sp_executesql @cmd, N'@sql nvarchar(max)', @sql
END
GO
EXEC getTableNames 'msdb';
PROS : Dynamic SQL is taken as a whole and no multiple concatenations need to be created.
CONS : It needs a little more work than a straight concatenation and can be seen as "obscure". Suffers from the same problems encountered with dynamic SQL, because the statement is again treated as a string.
System Object
Nice and easy: every stored procedure you create in the main database with the prefix "sp_" can be executed from any database context.
USE master;
GO
ALTER PROCEDURE sp_getTableNames
AS
BEGIN
SELECT name FROM sys.tables
END
GO
EXEC sys.sp_MS_marksystemobject 'sp_getTableNames'
GO
USE msdb;
GO
EXEC sp_getTableNames;
PROS : No need to use dynamic SQL.
CONS : Requires the creation of objects in the "master" database, which is something that is best avoided. Works only with stored.
I think this has already given to remove some of your doubts, here and here.