Differences between dynamic SQL statements and stored procedures

There are three types of stored procedures:
System stored procedure: (System stored procedure) prefixed with SP _, which is an SQL Server internal storage process.
Extended storage process: (Extended Stored Procedure), that is, plug-ins Program , Used to expand the sqlserver function, starting with SP _ or XP _, exists separately in the form of DLL.

(Both system stored procedures and extended stored procedures are in the master database. The name starting with SP _ is global and can be directly called by any database .)
System stored procedures are divided into the following types:

Directory stored procedures, such:
Sp_columns returns the column information of the specified table or view that can be queried in the current environment.
Sp_tables returns the list of objects that can be queried in the current environment (any objects that can be found in the from clause ).
Sp_stored_procedures returns the list of stored procedures in the current environment.

Replication-type stored procedures, such:
Sp_addarticle: Create a project and add it to the release. This stored procedure is executed on the Publishing Database of the Publishing Server.

Security management stored procedures, such:
Sp_addrole creates a new Microsoft SQL Server role in the current database.
Sp_password: add or change the password for logging on to Microsoft SQL Server.

Distributed Query stored procedures, such:
Sp_foreignkeys returns the Foreign keys that reference the primary keys on the linked server tables.
Sp_primarykeys returns the primary key column of the specified remote table. Each key column occupies one row.

Extended storage process:
Xp_sendmail sends emails and query result set attachments to specified recipients.
Xp_startmail starts the SQL Mail Client Session.
Xp_cmdshell executes the given command string in the command line interpreter of the operating system and returns any output in the text line. Grant non-administrative users the permission to execute xp_mongoshell.

User-Defined stored procedures:(User-Defined Stored Procedure), which is defined by the user in a specific database. The name should not start with SP _ or XP _ to prevent confusion.

Note:
1. In the stored procedure, some statements for object creation are not available: create default, create trigger, create procedure, create view, create rule.
2. In the same database, different owners can create object names with the same name. For example, data tables A. sample, B. sample, and C. Sample can exist simultaneously. If the owner of the object is not specified in the stored procedure (for example, the Select * from sample statement in the stored procedure, the sample in this sentence does not specify the owner), the default owner query order during execution is: the creator of the corresponding stored procedure-> the owner of the corresponding database. If the owner is not determined during the search, the system reports an error.
(Here I insert an additional sentence: If strict data operations are required, add the owner in any operation, such as leijun. Sample)
3. add ##or ## before the stored procedure name. The created stored procedure is a "temporary stored procedure" (# A local temporary stored procedure, ## a global temporary stored procedure ).

For dynamic SQL statementsArticle:
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/ODBC/htm/odbcdynamic_ SQL .asp
The reason for this is that the SQL statements to be executed can be determined only during running. It is slower than static SQL, so it does not use execute immediate, but uses sqlda technology's prepared execution method for execution, it has the advantage of one declaration (space allocation), with the performance between static SQL and full dynamic SQL (directly executed by dynamic combinations.

Compared with dynamic SQL, stored procedures have the following advantages:
1. stored procedures allow standard component programming
After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure.
Statement and database professionals can modify the stored procedure at any timeSource codeNo impact
For application sourceCodeContains only the calling statements of the stored procedure, which greatly improves the portability of the program.

2. Fast execution of Stored Procedures
If an operation contains a large number of transaction-SQL code or is executed multiple times
The execution speed is much faster than that of batch processing because the stored procedure is pre-compiled and checked when a stored procedure is run for the first time.
The query optimizer analyzes and optimizes it and provides the transaction-
SQL statements must be compiled and optimized each time they are run. Therefore, the speed is relatively slow.

3. stored procedures can reduce network traffic
For operations on the same data database object, such as querying and modifying, if the operation involves
The transaction-SQL statement is organized into a stored procedure. When the stored procedure is called on the client's computer
Only this call statement is transmitted in the network. Otherwise, multiple SQL statements will be sent, greatly increasing the network traffic drop.
Low network load

4. stored procedures can be used as a security mechanism to make full use of
the system administrator can restrict the permissions to execute a stored procedure to implement corresponding data access
permission restriction to prevent unauthorized users from accessing data to ensure data security