Get three methods of SQL Server stored procedure definition _mssql

The concept of stored procedures

Stored procedure procedure is a set of SQL statements that are compiled and stored in a database to complete a specific function, and are executed by specifying the name of the stored procedure and giving the parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in a database when they are created, stored procedures run faster than a single block of SQL statements. At the same time, it can reduce network traffic and simple network burden to a certain extent because it only needs to provide the stored procedure name and the necessary parameter information in the call.

Advantages of stored Procedures

A, stored procedures allow standard component-type programming

After a stored procedure is created, it can be executed multiple times in a program without having to rewrite the SQL statement of the stored procedure. and database professionals can modify the stored procedures at any time, but have no effect on the application source code, thereby greatly improving the portability of the program.

B, the stored procedure can achieve faster execution speed

If an action contains a large number of T-SQL statement code that is executed multiple times, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives a storage plan in the system table that is ultimately present when a stored procedure is first run. The T-SQL statements for batches need to be precompiled and optimized each time, so the speed is slower.

C, stored procedures to reduce network traffic

For the same operation against a database object, if the T-SQL statement involved in the operation is organized into a stored procedure, when the stored procedure is invoked on the client, the network is passed only by the calling statement, otherwise it will be more than one SQL statement. This reduces network traffic and lowers Network load.

D, stored procedures can be used as a security mechanism to fully utilize

A system administrator can restrict the execution of one stored procedure, thereby restricting access to certain data, preventing unauthorized users from accessing data, and ensuring data security.

The above to introduce the concept of stored procedures and stored procedures of the advantages are to pave the way for this article, focusing on the SQL Server stored procedures to introduce the definition of three ways, together to see!

First type:

DECLARE @p_text varchar (max)
SELECT @p_text = text from 
syscomments 
WHERE id = (SELECT ID from sysobjects wher E name = ' Stored procedure name ')

However, when the contents of the stored procedure is relatively long, the contents of text are not completely displayed.

The second type:

DECLARE @p_text varchar (max)
SELECT @p_text = definition from sys.sql_modules 
JOIN sys.objects on Sys.sql_ modules.object_id=sys.objects.object_id--and type= ' P ' and 
sys.objects.name= ' Sp_msupd_dboind_ Independencereportto ' 
print @p_text

The content is well-formed, but print itself cannot display more than 8000 bytes of content, so the stored procedure content is too long.

The third type:

exec sp_helptext ' [Sp_msupd_dborpt_customer] '

Returns the content of the definition but a single line of text that can be used to solve the problem.

The above is a small set for you to get the definition of SQL Server stored procedures three ways to help, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!