What is a sql stored procedure. Source data for examples. Solutions Collecting From Web of "What is the difference between a stored procedure and a view?"
Stored procedure - a database object, which is a set of SQL instructions that is compiled once and stored on the server. Stored procedures are very similar to ordinary high-level language procedures, they can have input and output parameters and local variables, they can perform numeric calculations and operations on character data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, stored procedures allow loops and branches, that is, they can use instructions to control the execution process.
Stored procedures are similar to user-defined functions (UDFs). The main difference is that user-defined functions can be used like any other expression in a SQL statement, while stored procedures must be called using the CALL function:
CALL procedure(…)
EXECUTE procedure(…)
Stored procedures can return multiple results, that is, the results of a SELECT query. Such result sets can be processed using cursors, other stored procedures that return a result set pointer, or applications. Stored procedures can also contain declared variables for processing data and cursors, which allow you to loop over multiple rows in a table. The SQL standard provides IF, LOOP, REPEAT, CASE, and many others to work with. Stored procedures can accept variables, return results, or modify variables and return them, depending on where the variable is declared.
The implementation of stored procedures varies from one DBMS to another. Most major database vendors support them in one form or another. Depending on the DBMS, stored procedures can be implemented in various programming languages, such as SQL, Java, C or C++. Stored procedures not written in SQL may or may not execute SQL queries on their own.
Behind
Sharing logic with other applications. Stored procedures encapsulate functionality; this provides connectivity for data access and management across different applications.
Isolating users from database tables. This allows you to give access to stored procedures, but not to the table data itself.
Provides a protection mechanism. As per the previous point, if you can only access data through stored procedures, no one else can erase your data through the SQL DELETE command.
Improved execution as a result of reduced network traffic. Using stored procedures, multiple queries can be combined.
Against
Increased load on the database server due to the fact that most of the work is performed on the server side, and less on the client side.
You'll have to learn a lot. You will need to learn MySQL expression syntax to write your stored procedures.
You are duplicating your application logic in two places: server code and code for stored procedures, thereby complicating the process of data manipulation.
Migration from one DBMS to another (DB2, SQL Server etc.) can lead to problems.
Purpose and Benefits of Stored Procedures
Stored procedures improve performance, enhance programming capabilities, and support data security features.
Instead of storing a frequently used query, clients can reference the corresponding stored procedure. When a stored procedure is called, its contents are immediately processed by the server.
In addition to actually executing the query, stored procedures also allow you to perform calculations and manipulate data - changing, deleting, executing DDL statements (not in all DBMSs!) and calling other stored procedures, and performing complex transactional logic. A single statement allows you to call a complex script contained in a stored procedure, avoiding sending hundreds of commands across the network and, in particular, the need to transfer large amounts of data from the client to the server.
In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated). In the future, its processing is faster. The Oracle DBMS interprets stored procedural code stored in the data dictionary. Starting with Oracle 10g, the so-called native compilation of stored procedural code in C and then into the machine code of the target machine is supported, after which, when a stored procedure is called, its compiled object code is directly executed.
Programming capabilities
The created stored procedure can be called at any time, which provides modularity and encourages code reuse. The latter makes the database easier to maintain, since it becomes isolated from changing business rules. You can modify a stored procedure in accordance with the new rules at any time. After this, all applications using it will automatically come into compliance with the new business rules without direct modification.
Safety
The use of stored procedures allows you to limit or completely eliminate direct user access to database tables, leaving users with only permissions to execute stored procedures that provide indirect and strictly regulated access to data. In addition, some DBMSs support text encryption (wrapping) of a stored procedure.
These security features allow the database structure to be isolated from the user, ensuring database integrity and reliability.
The likelihood of actions such as SQL injection is reduced because well-written stored procedures additionally check input parameters before passing the query to the DBMS.
Implementing Stored Procedures
Stored procedures are usually created using the SQL language or its specific implementation in the selected DBMS. For example, for these purposes, in the Microsoft SQL Server DBMS there is the Transact-SQL language, in Oracle - PL/SQL, in InterBase and Firebird - PSQL, in PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, in IBM DB2 - SQL/PL (English), in Informix - SPL. MySQL follows the SQL:2003 standard quite closely, its language is similar to SQL/PL.
Some DBMSs allow the use of stored procedures written in any programming language that can create independent executable files, for example, C++ or Delphi. In Microsoft SQL Server terminology, such procedures are called extended stored procedures and are simply functions contained in a Win32 DLL. And, for example, in Interbase and Firebird, functions called from DLL/SO have a different name - UDF (User Defined Function). MS SQL 2005 introduced the ability to write stored procedures in any .NET language, and extended stored procedures are planned to be abandoned in the future. The Oracle DBMS, in turn, allows writing stored procedures in Java language. In IBM DB2, writing stored procedures and functions in conventional programming languages is a traditional way, supported from the very beginning, and the SQL procedural extension was added to this DBMS only in fairly late versions, after its inclusion in the ANSI standard. Informix also supports procedures in Java and C.
In the Oracle DBMS, stored procedures can be combined into so-called packages. A package consists of two parts - a package specification, which specifies the definition of a stored procedure, and a package body, which contains its implementation. Thus, Oracle allows you to separate the interface of the program code from its implementation.
In the IBM DB2 DBMS, stored procedures can be combined into modules.
Syntax
CREATE PROCEDURE `p2`()
SQL SECURITY DEFINER
COMMENT "A procedure"
SELECT "Hello World !";
The first part of the code creates a stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.
4 characteristics of a stored procedure:
Language: For portability purposes, the default is SQL.
Deterministic: if the procedure always returns the same result and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.
SQL Security: user rights are checked during the call. INVOKER is the user calling the stored procedure. DEFINER is the “creator” of the procedure. The default value is DEFINER.
Comment: For documentation purposes, the default value is ""
Calling a Stored Procedure
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , "string parameter" , @parameter_var);
Modifying a Stored Procedure
MySQL has an ALTER PROCEDURE statement for changing procedures, but it is only suitable for changing certain characteristics. If you need to change the parameters or body of a procedure, you should delete and recreate it.
Removalstoredprocedures
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS statement catches an error if such a procedure does not exist.
Options
CREATE PROCEDURE proc1(): empty parameter list
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): one input parameter. The word IN is optional because the default parameters are IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter returned.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and return.
The variable declaration syntax looks like this:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
When working with SQL Server, users can create their own procedures that implement certain actions. Stored procedures are full-fledged database objects, and therefore each of them is stored in a specific database. A direct call to a stored procedure is only possible if it is done in the context of the database where the procedure is located.
Types of Stored Procedures
SQL Server has several types of stored procedures.
System stored procedures are designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that system stored procedures are an interface that provides work with system tables, which ultimately comes down to changing, adding, deleting and retrieving data from system tables of both user and system databases. System stored procedures are prefixed with sp_, are stored in the system database, and can be called in the context of any other database.
Custom stored procedures implement certain actions. Stored procedures are a full-fledged database object. As a result, each stored procedure is located in a specific database, where it is executed.
Temporary stored procedures exist only for a short time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they are created. When creating such a procedure, you must give it a name starting with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects or the server is restarted or stopped. Global temporary stored procedures are available to any connection from a server that has the same procedure. To define it, just give it a name starting with the characters ##. These procedures are deleted when the server is restarted or stopped, or when the connection in the context in which they were created is closed.
Triggers
Triggers are a type of stored procedure. They are executed when a data manipulation language (DML) operator is executed on the table. Triggers are used to check data integrity and also to roll back transactions.
Trigger is a compiled SQL procedure, the execution of which is conditioned by the occurrence of certain events within the relational database. The use of triggers is, for the most part, very convenient for database users. Still, their use often involves additional resource costs for I/O operations. When the same results (with much less overhead) can be achieved using stored procedures or application programs, the use of triggers is not practical.
Triggers is a special SQL server tool used to maintain data integrity in a database. Integrity constraints, rules, and defaults may not always achieve the desired level of functionality. It is often necessary to implement complex data verification algorithms to ensure their reliability and reality. In addition, sometimes you need to monitor changes in table values so that the associated data can be modified as needed. Triggers can be thought of as a kind of filters that come into effect after all operations have been completed in accordance with rules, standard values, etc.
Trigger is a special type of stored procedure that is launched automatically by the server when an attempt is made to change data in tables to which triggers are associated. Every Trigger is tied to a specific table. All data modifications it makes are considered as one transaction. If an error or data integrity violation is detected, the transaction is rolled back. Changes are therefore prohibited. Any changes already made by the trigger are also undone.
Creates trigger only the database owner. This restriction allows you to avoid accidental changes to the structure of tables, ways of connecting other objects to them, etc.
Trigger It is a very useful and at the same time dangerous remedy. So, if the logic of its operation is incorrect, you can easily destroy an entire database, so triggers must be debugged very carefully.
Unlike a regular subroutine, trigger is executed implicitly whenever a trigger event occurs, and it has no arguments. Activating it is sometimes called firing a trigger. Using triggers, the following goals are achieved:
Validating the correctness of data entered and enforcing complex data integrity constraints that are difficult, if not impossible, to maintain using integrity constraints set on a table;
issuing warnings reminding you to perform certain actions when updating a table implemented in a certain way;
accumulation of audit information by recording information about the changes made and those persons who performed them;
replication support.
The basic format of the CREATE TRIGGER command is shown below:
<Определение_триггера>::=
CREATE TRIGGER trigger_name
BEFORE | AFTER<триггерное_событие>
ON<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Trigger events consist of inserting, deleting, and updating rows in a table. In the latter case, you can specify specific table column names for the trigger event. The timing of the trigger is determined using the BEFORE keywords ( Trigger runs before the events associated with it are executed) or AFTER (after they are executed).
The actions performed by the trigger are specified for each row (FOR EACH ROW) covered by the event, or only once for each event (FOR EACH STATEMENT).
Incorrectly written triggers can lead to serious problems, such as dead locks. Triggers can block many resources for a long time, so you should pay attention to Special attention to minimize access conflicts.
Trigger can be created only in the current database, but it is possible to access other databases within the trigger, including those located on a remote server.
1. Include a line in your procedures - SET NOCOUNT ON: With each DML expression, SQL server carefully returns us a message containing the number of processed records. This information It may be useful to us while debugging the code, but after that it will be completely useless. By writing SET NOCOUNT ON, we disable this function. For stored procedures containing multiple expressions or/and loops, this action can give a significant performance increase, because the amount of traffic will be significantly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Here is the procedure code
SELECT column1 FROM dbo.TblTable1
--Toggle SET NOCOUNT to initial state
SET NOCOUNT OFF;
GO
2. Use the schema name with the object name: Well, I think it’s clear. This operation tells the server where to look for objects and instead of randomly rummaging through its bins, it will immediately know where it needs to go and what to take. With a large number of databases, tables and stored procedures, it can significantly save our time and nerves.
SELECT * FROM dbo.MyTable --This is good to do
-- Instead of
SELECT * FROM MyTable --And doing this is bad
--Procedure call
EXEC dbo.MyProc --Good again
--Instead of
EXEC MyProc --Bad!
3. Do not use the “sp_” prefix in the name of your stored procedures: If our procedure name starts with "sp_", SQL Server will look in its main database first. The fact is that this prefix is used for personal internal stored procedures of the server. Therefore, its use may lead to additional costs and even incorrect results if a procedure with the same name as yours is found in its database.
4. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *): To check for the existence of a record in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the internal expression, it doesn’t matter “1”, all columns or the table. The returned data is basically not used in any way. Thus, to compress traffic during data transmission, it is more logical to use “1”, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")
5. Use TRY-Catch to catch errors: Before 2005 servers, after each request, a huge number of error checks were written in the procedure. More code always consumes more resources and more time. With 2005 SQL Server, a more correct and convenient way solutions to this problem:
BEGIN TRY
--code
END TRY
BEGIN CATCH
--error catching code
END CATCH
Conclusion
Basically, that's all I have for today. I repeat once again that here are only those techniques that I personally used in my practice, and I can vouch for their effectiveness.
P.S.
My first post, don't judge too harshly.
SQL stored procedures are executable program modules that can be stored in the form of various objects. In other words, it is an object that contains SQL statements. These stored procedures can be executed in the client application programs to get good performance. In addition, such objects are often called from other scripts or even from some other section.
Introduction
Many people believe that they are similar to various procedures (respectively, except MS SQL). Perhaps this is true. They have similar parameters and can produce similar values. Moreover, in some cases they touch. For example, they are combined with DDL and DML databases, as well as user functions (codenamed UDF).
In reality, SQL stored procedures have a wide range of advantages that set them apart from similar processes. Security, programming flexibility, productivity - all this attracts more and more users working with databases. The peak popularity of procedures occurred in 2005-2010, when a program from Microsoft called “SQL Server Management Studio” was released. With its help, working with databases has become much easier, more practical and convenient. From year to year, this one gained popularity among programmers. Today it is an absolutely familiar program, which for users who “communicate” with databases is on a par with Excel.
When a procedure is called, it is instantly processed by the server itself without unnecessary processes or user intervention. After this, you can carry out any deletion, execution, or modification. The DDL operator is responsible for all this, who alone performs the most complex actions to process objects. Moreover, all this happens very quickly, and the server is not actually loaded. This speed and performance allows you to very quickly transfer large amounts of information from the user to the server and vice versa.
To implement this technology for working with information, there are several programming languages. These include, for example, PL/SQL from Oracle, PSQL in the InterBase and Firebird systems, as well as the classic Microsoft Transact-SQL. All of them are designed for creating and executing stored procedures, which allows large database processors to use their own algorithms. This is also necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, the creation, modification or deletion of certain data.
Productivity
These database objects can be programmed in a variety of ways. This allows users to choose the type of method used that is most suitable, saving effort and time. In addition, the procedure is processed itself, which avoids the huge time spent on communication between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at absolutely any time. It is especially worth noting the speed with which the SQL stored procedure is launched: this process occurs faster than others similar to it, which makes it convenient and universal.
Safety
This type of information processing differs from similar processes in that it guarantees increased security. This is ensured by the fact that access to procedures by other users can be completely excluded. This will allow the administrator to carry out operations with them independently, without fear of interception of information or unauthorized access to the database.
Data transfer
The relationship between the SQL stored procedure and the client application is the use of parameters and return values. The latter does not have to pass the data to the stored procedure, but this information (mainly at the user's request) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, optionally) to the application that called it, using various methods, with the help of which both a call to a stored SQL procedure and a return can be made, for example:
Transferring data using an Output type parameter;
Passing data using the return operator;
Passing data using the select operator.
Now let’s figure out what this process looks like from the inside.
1. Create an EXEC stored procedure in SQL
You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be listed in the programmable node of the database, in which the creation procedure is executed by the operator. To execute, SQL stored procedures use an EXEC process that contains the name of the object itself.
When you create a procedure, its name appears first, followed by one or more parameters assigned to it. Parameters may be optional. After the parameter(s), that is, the body of the procedure, have been written, some necessary operations need to be performed.
The point is that a body can have local variables located in it, and these variables are also local in relation to procedures. In other words, they can only be viewed within the body of a Microsoft SQL Server procedure. Stored procedures in this case are considered local.
So, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that a great option in this case is to create and execute a procedure with the schema name in the classifier.
The body of the procedure can be of any kind, such as creating a table, inserting one or more rows of a table, establishing the type and nature of the database, and so on. However, the procedure body restricts certain operations from being performed within it. Some of the important limitations are listed below:
The body should not create any other stored procedure;
The body should not create a false impression of the object;
The body should not create any triggers.
2. Setting a variable in the body of the procedure
You can make variables local to the body of the procedure, and then they will reside exclusively within the body of the procedure. It is good practice to create variables at the beginning of the stored procedure body. But you can also set variables anywhere in the body of a given object.
Sometimes you may notice that several variables are set on one line, and each variable parameter is separated by a comma. Also note that the variable is prefixed with @. In the body of the procedure you can set the variable to wherever you want. For example, the @NAME1 variable may be declared near the end of the procedure body. To assign a value to a declared variable, a set of personal data is used. Unlike the situation where more than one variable is declared on the same line, in this situation only one set of personal data is used.
Users often ask the question: “How to assign multiple values in one statement in the body of a procedure?” Well. It's an interesting question, but it's much easier to do than you think. Answer: Using pairs such as "Select Var = value". You can use these pairs by separating them with a comma.
A variety of examples show people creating a simple stored procedure and executing it. However, a procedure can accept parameters such that the process calling it will have values close to it (but not always). If they coincide, then corresponding processes begin inside the body. For example, if you create a procedure that will accept a city and region from the caller and return data about how many authors belong to the corresponding city and region. The procedure will query the database's author tables, such as Pubs, to perform this author count. To get these databases, for example, Google downloads the SQL script from the SQL2005 page.
In the previous example, the procedure takes two parameters, which English language conditionally will be called @State and @City. The data type matches the type defined in the application. The body of the procedure has internal variables @TotalAuthors, and this variable is used to display the number of authors. Next comes the query selection section, which calculates everything. Finally, the calculated value is printed in the output window using the print statement.
How to execute a stored procedure in SQL
There are two ways to perform the procedure. The first way shows, by passing parameters, how a comma-separated list is executed after the procedure name. Let's say we have two values (as in the previous example). These values are collected using the @State and @City procedure parameter variables. In this method of passing parameters, order is important. This method is called ordinal argument passing. In the second method, the parameters are already directly assigned, and in this case the order is not important. This second method is known as passing named arguments.
The procedure may deviate slightly from the typical one. Everything is the same as in the previous example, but only here the parameters are shifted. That is, the @City parameter is stored first, and @State is stored next to the default value. The default parameter is usually highlighted separately. SQL stored procedures are passed as just parameters. In this case, provided, the parameter "UT" replaces the default value "CA". In the second execution, only one argument value is passed for the @City parameter, and the @State parameter takes on the default value of "CA". Experienced programmers advise that all variables should be located towards the end of the parameter list by default. Otherwise execution is not possible, and then you have to work with passing named arguments, which is longer and more complex.
4. SQL Server Stored Procedures: Return Methods
There are three important ways to send data in a called stored procedure. They are listed below:
Return the value of a stored procedure;
Stored procedure parameter output;
Selecting one of the stored procedures.
4.1 Returning values from SQL stored procedures
In this technique, a procedure assigns a value to a local variable and returns it. A procedure can also directly return a constant value. In the following example, we have created a procedure that returns total number authors. If you compare this procedure with the previous ones, you can see that the print value is reversed.
Now let's see how to execute a procedure and print its return value. Executing the procedure requires setting a variable and printing, which is carried out after this entire process. Note that instead of a print statement, you can use a Select statement, such as Select @RetValue as well as OutputValue.
4.2 SQL Stored Procedure Parameter Output
The response value can be used to return a single variable, which is what we saw in the previous example. Using the Output parameter allows a procedure to send one or more variable values to the caller. The output parameter is designated precisely by this keyword “Output” when creating a procedure. If a parameter is given as an output parameter, then the procedure object must assign a value to it. SQL stored procedures, examples of which can be seen below, in this case return with summary information.
In our example there will be two output names: @TotalAuthors and @TotalNoContract. They are indicated in the list of parameters. These variables assign values within the body of the procedure. When we use output parameters, the caller can see the value set inside the body of the procedure.
Also, in the previous scenario, two variables are declared to see the values that the MS SQL Server stored procedures set in the output parameter. Then the procedure is performed by supplying the normal value of the “CA” parameter. The following options are output and therefore the declared variables are passed in the prescribed order. Note that when passing through the variables the output keyword also asked here. After the procedure is completed successfully, the values returned by the output parameters are displayed in the message window.
4.3 Selecting one of the SQL stored procedures
This technique is used to return a set of values as a data table (RecordSet) to the calling stored procedure. In this example, the SQL stored procedure with @AuthID parameters queries the Authors table by filtering the records returned using that @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure is executed, the AuthId is passed back. This procedure here always returns only one record or none at all. But a stored procedure does not have any restrictions on returning more than one record. It is not uncommon to see examples where data is returned using selected parameters involving calculated variables by providing multiple totals.
Finally
A stored procedure is a fairly serious program module that returns or passes, and also sets the necessary variables thanks to the client application. Because the stored procedure runs itself on the server, huge amounts of data exchange between the server and the client application (for some calculations) can be avoided. This allows you to reduce the load on SQL server, which, of course, benefits their holders. One of the subtypes is T SQL stored procedures, but their study is necessary for those who create impressive databases. There are also a large, even huge number of nuances that can be useful when studying stored procedures, but this is needed more for those who plan to get involved in programming, including professionally.
Procedure declaration
CREATE PROCEDURE [({IN|OUT|INOUT} [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC]
END
Keywords
. IN (Input) – input parameter
. OUT (Output) – output parameter
. INOUT – input and output, as well as a field (without parameters)
. DYNAMIC RESULT SET indicates that the procedure can open a specified number of cursors that will remain open after the procedure returns
Notes
It is not recommended to use many parameters in stored procedures (primarily large numbers and character strings) due to network and stack overload. In practice, existing dialects of Transact-SQL, PL/SQL and Informix have significant differences from the standard, both in the declaration and use of parameters, in the declaration of variables, and in the call of subroutines. Microsoft recommends using the following approximation to estimate the stored procedure cache size:
=(maximum number of concurrent users)*(size of largest execution plan)*1.25. Determining the size of the execution plan in pages can be done using the command: DBCC MEMUSAGE.
Calling a procedure
In many existing DBMSs, stored procedures are called using the operator:
EXECUTE PROCEDURE [(][)]
Note: Calls to stored procedures can be made from within an application, another stored procedure, or interactively.
Example of a procedure declaration
CREATE PROCEDURE Proc1 AS //declare the procedure
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //declare the cursor
OPEN Cur1 //open the cursor
FETCH NEXT FROM Cur1 //read data from the cursor
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //close the cursor
DEALLOCATE Cur1
EXECUTE Proc1 //run the procedure
Polymorphism
Two subroutines with the same name can be created in the same schema if the parameters of the two subroutines are sufficiently different from each other that they can be distinguished. To distinguish between two routines with the same name in the same schema, each is given an alternative and unique name (specific name). Such a name may be explicitly specified when the subroutine is defined. When calling subroutines with several identical names, determining the required subroutine is carried out in several steps:
. Initially, all procedures with the specified name are defined, and if there are none, then all functions with the given name.
. For further analysis, only those subroutines in relation to which this user has the execution privilege (EXECUTE).
. For them, those whose number of parameters corresponds to the number of call arguments are selected. The specified data types of the parameters and their positions are checked.
. If there is more than one subroutine left, then the one whose qualifying name is shorter is selected.
In practice, in Oracle polymorphism is supported for functions declared only in a package, DB@ - in different schemas, and in Sybase and MS SQL Server overloading is prohibited.
Deleting and changing procedures
To remove a procedure, use the operator:
To change a procedure, use the operator:
ALTER PROCEDURE [([{IN|OUT|INOUT}])]
BEGIN [ATOMIC]
END
Privileges to perform procedures
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]
System procedures
Many DBMSs (including SQL Server) have a specific set of built-in system stored procedures that you can use for your own purposes.