A: Overview
SQL statements that are created and run at runtime in response to input parameters are known as dynamic SQL. Coding flexibility is increased by using dynamic SQL statements, however programming and maintenance will become more difficult. Additionally, a success concept like SQL Injection will be introduced (see What Is SQL Injection).
In general, we would say, dynamic SQL Script is no good, due to, at least, two reasons:
- Not readable, and not maintainable;
- Security, possible the target of SQL injection
Therefore, using dynamic SQL Script must be in the situation: 'have to'.
In our previous SDD database data input, Create a System Data Dictionary Database, we use dynamic SQL. Let us discuss the reasonabilities. The content of this article:
- A - Introduction
- B - Security is not a concern for using Dynamic SQL in a Stored Procedure
- C - Requirement: Loop for Servers and Databases
- C.1 - Get Servers (including linnked servers and remote servers) by SQL
- C.2 - Get Databases by SQL
- D - Looping Databases by Static SQL is not possible
- D.1 - All system database, such as sys.object, are all within one database, except servers and databases,
- D.2 - The USE key word cannot be used within a Stored Procedure
- D.3 - The database Identifier cannot be parameterized.
- E - Dynamic SQL Script is the only choice for Looping databases
B - Security is not a concern for using Dynamic SQL in a Stored Procedure
First, due to used in Stored Procedure, within firewall, and especially, there is no parameter input, so the security issue is almost zero. Let us see if this is a 'have to' situation.
C - Requirement: Loop for Servers and Databases
We need to create a database including all information about the whole databases in a Server, or at least one instance. We need to dynamically loop the databases existing in this server or instance.This is possible by
C.1 - Get Servers (including linnked servers and remote servers) by SQL
SELECT * FROM sys.servers
Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0. Such as
C.2 - Get Databases by SQL
SELECT * FROM sys.databases
Contains one row per database in the instance of SQL Server.
D - Looping Databases by Static SQL is not possible
Due to the following reasons:
- All system database, such as sys.object, are all within one database, except servers and databases,
- The USE key word cannot be used within a Stored Procedure
- The database Identifier cannot be parameterized.
We show these one by one.
D.1 - All system database, such as sys.object, are all within one database, except servers and databases,
In this page: sys.sysobjects , the definitiion says:
This ontains one row for each object that is created within a database
D.2 - The USE key word cannot be used within a Stored Procedure:
a USE database statement is not alloweed in a procedure, function or trigger:
if we try to use a USE statement in a stored procedure, we will see the error like above.
D.3 - we cannot use the dynamic database name in the schema identifier, such as
E - Dynamic SQL Script is the only choice for Looping databases
The following code sample is from the previous article: Create a System Data Dictionary Database
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
