For programming extended stored procedures, Microsoft provides the ODS (Open Data Service) API, a set of macros and functions used to build server applications that allow you to expand the functionality of MS SQL Server 2000.
Extended stored procedures are ordinary functions written in C/C++ using the ODS API and WIN32 API, designed as a dynamic link library (dll) and designed, as I already said, to expand the functionality of the SQL server. The ODS API provides the developer with a rich set of functions that allows you to transfer data to the client received from any external data source in the form of regular record sets. Also, an extended stored procedure can return values through the parameter passed to it (OUTPUT parameter).
How extended stored procedures work.
- When a client application calls an extended stored procedure, the request is transmitted in TDS format through the Net-Libraries and Open Data Service to the MS SQL SERVER core.
- SQL Sever finds the dll library associated with the name of the extended stored procedure and loads it into its context, if it has not been loaded there before, and calls the extended stored procedure implemented as a function inside the dll.
- The extended stored procedure performs the necessary actions on the server and transfers a set of results to the client application using the service provided by the ODS API.
Features of extended stored procedures.
- Extended stored procedures are functions that execute in the MS SQL Server address space and in the security context of the account under which the MS SQL Server service is running;
- Once an extended stored procedure dll has been loaded into memory, it remains there until SQL Server is stopped, or until an administrator forces it to unload it using the command:
DBCC DLL_name (FREE). - An extended stored procedure is executed in the same way as a regular stored procedure:
EXECUTE xp_extendedProcName @param1, @param2 OUTPUT
@param1 input parameter
@param2 input/output parameter
Since extended stored procedures run in the address space of the MS SQL Server service process, any critical errors problems that arise in their operation can damage the server core, so it is recommended to thoroughly test your DLL before installing it on a production server.
Creating extended stored procedures.
An extended stored procedure is a function that has the following prototype:
SRVRETCODE xp_extendedProcName(SRVPROC * pSrvProc);
Parameter pSrvProc A pointer to an SRVPROC structure, which is a handle for each specific client connection. The fields in this structure are undocumented and contain information that the ODS library uses to manage communication and data between the Open Data Services server application and the client. In any case, you do not need to access this structure, much less modify it. This parameter is required to be specified when calling any ODS API function, so I will not dwell on its description further.
The use of the xp_ prefix is optional, but there is a convention to start the name of an extended stored procedure this way to distinguish it from a regular stored procedure, which, as you know, begins its names with the sp_ prefix.
You should also remember that extended stored procedure names are case sensitive. Don't forget about this when you call an extended stored procedure, otherwise you will receive an error message instead of the expected result.
If you need to write dll initialization/deinitialization code, use the standard DllMain() function for this. If you do not have such a need, and you do not want to write DLLMain(), then the compiler will build its own version of the DLLMain() function, which does nothing but simply returns TRUE. All functions called from a dll (ie extended stored procedures) must be declared as exportable. If you write in MS Visual C++, use the directive __declspec(dllexport). If your compiler does not support this directive, describe the exported function in the EXPORTS section of the DEF file.
So, to create a project, we will need the following files:
- Srv.h header file, contains a description of the ODS API functions and macros;
- Opends60.lib is an import file for the Opends60.dll library, which implements the entire service provided by the ODS API.
Declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
When MS SQL Server loads a DLL with an extended stored procedure, it first calls this function to obtain information about the version of the library being used.
To write your first extended stored procedure, you will need to install on your computer:
MS SQL Server 2000 of any edition (I have Personal Edition). During the installation process, be sure to select the source sample option
- MS Visual C++ (I used version 7.0), but I know for sure 6.0 will do
Installing SQL Server -a is needed to test and debug your DLL. Debugging over the network is also possible, but I have never done this, so I installed everything on my local disk. In delivery Microsoft Visual C++ 7.0 Interprise Edition includes the Extended Stored Procedure DLL Wizard. In principle, it does not do anything extra natural, but only generates a template template for an extended stored procedure. If you like masters, you can use it. I prefer to do everything by hand, and therefore I will not consider this case.
Now to the point:
- Launch Visual C++ and create new project- Win32 Dynamic Link Library.
- Include a header file in the project - #include
- Go to the Tools => Options menu and add search paths for include and library files. If you did not change anything when installing MS SQL Server, then specify:
C:Program FilesMicrosoft SQL Server80ToolsDevToolsInclude for header files;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib for library files.
- Specify the name of the library file opends60.lib in the linker options.
At this point the preparatory stage is completed, you can start writing your first extended stored procedure.
Statement of the problem.
Before you start programming, you need to have a clear idea of where to start, what the end result should be, and how to achieve it. So, here's the technical specification:
Develop an extended stored procedure for MS SQL Server 2000 that receives full list users registered in the domain, and returns it to the client in the form of a standard record set. As the first input parameter, the function receives the name of the server containing the catalog database ( Active Directory), i.e. the name of the domain controller. If this parameter is NULL, then a list of local groups must be passed to the client. The second parameter will be used by the extended stored procedure to return the value of the result of successful/unsuccessful operation (OUTPUT parameter). If the extended stored procedure is completed successfully, then it is necessary to pass the number of records returned to the client record set; if during operation it was not possible to obtain the required information, the value of the second parameter must be set to -1, as a sign of unsuccessful completion.
The conditional prototype of an extended stored procedure is as follows:
xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);
And here is the extended stored procedure template that we need to fill with content:
#include
#include
#define XP_NOERROR 0
#define XP_ERROR -1
__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{
//Checking the number of passed parameters
//Checking the type of passed parameters
//Checking if parameter 2 is an OUTPUT parameter
//Checking if parameter 2 is long enough to store the value
//Getting input parameters
//Getting a list of users
// Sending the received data to the client in the form of a standard record set
//Setting the value of the OUTPUT parameter
return (XP_NOERROR);
}
Working with input parameters
In this chapter, I do not want to scatter your attention on extraneous things, but I want to focus it on working with the parameters passed to the extended stored procedure. Therefore, we will somewhat simplify our technical specifications and develop only that part of it that works with input parameters. But first, not much theory
The first action our exteneded stored procedure must perform is to receive the parameters that were passed to it when it was called. Following the above algorithm, we need to perform the following steps:
Determine the number of passed parameters;
- Make sure that the passed parameters have the correct data type;
- Make sure that the specified OUTPUT parameter is of sufficient length to store in it the value returned by our extended stored procedure.
- Receive the passed parameters;
- Set the output parameter values as a result of successful/unsuccessful completion of the extended stored procedure.
Now let's look at each point in detail:
Determining the number of parameters passed to an extended stored procedure
To get the number of parameters passed, you must use the function:
int srv_rpcparams(SRV_PROC * srvproc);
If successful, the function returns the number of parameters passed to the extended stored procedure. If the extended stored procedure was called without parameters, srv_rpcparams will return -1. Parameters can be passed by name or by position (unnamed). In any case, these two methods should not be mixed. An attempt to pass input parameters to a function by name and by position at the same time will result in an error and srv_rpcparams will return 0.
Determining the data type and length of passed parameters
To obtain information about the type and length of transmitted Microsoft settings recommends using the srv_paramifo function. This generic function replaces the srv_paramtype, srv_paramlen, srv_parammaxlen calls, which are now deprecated. Here is its prototype:
int srv_paraminfo(
SRV_PROC * srvproc,
int n,
BYTE * pbType,
ULONG* pcbMaxLen,
ULONG * pcbActualLen,
BYTE * pbData,
BOOL * pfNull);
pbType specifies the serial number of the parameter. The number of the first parameter starts from 1.
pcbMaxLen pointer to a variable into which the function stores the maximum value of the parameter length. This value is due specific type data of the passed parameter, we will use it to make sure that the OUTPUT parameter is of sufficient length to store the transmitted data.
pcbActualLen pointer to the real length of the parameter passed to the extended stored procedure when called. If the passed parameter has zero length and the pfNull flag is set to FALSE then (* pcbActualLen) ==0.
pbData- a pointer to a buffer for which memory must be allocated before calling srv_paraminfo. In this buffer, the function places the input parameters received from the extended stored procedure. The buffer size in bytes is equal to the pcbMaxLen value. If this parameter is set to NULL, no data is written to the buffer, but the function correctly returns the values *pbType, *pcbMaxLen, *pcbActualLen, *pfNull. Therefore, you need to call srv_paraminfo twice: first with pbData=NULL, then, having allocated the required memory size for a buffer equal to pcbActualLen, call srv_paraminfo a second time, passing a pointer to the allocated memory block to pbData.
pfNull pointer to a NULL flag. srv_paraminfo sets it to TRUE if the value of the input parameter is NULL.
Checking if the second OUTPUT parameter is a parameter.
The srv_paramstatus() function is designed to determine the status of the passed parameter:
int srv_paramstatus (
SRV_PROC * srvproc,
int n
);
n is the number of the parameter passed to the extended stored procedure when called. Let me remind you: parameters are always numbered starting from 1.
To return a value, srv_paramstatus uses the zero bit. If it is set to 1, the passed parameter is an OUTPUT parameter, if set to 0, it is a normal parameter passed by value. If the exteneded stored procedure was called without parameters, the function will return -1.
Setting the value of the output parameter.
The output parameter passed to the extended stored one can be given a value using the srv_paramsetoutput function. This new feature replaces the call to the srv_paramset function, which is now deprecated because does not support new data types introduced in the ODS API and zero-length data.
int srv_paramsetoutput(
SRV_PROC *srvproc,
int n,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);
pbData pointer to a buffer with data that will be sent to the client to set the value of the output parameter.
cbLen length of the sent data buffer. If the data type of the parameter passed to OUTPUT specifies constant-length data and does not allow storage of a NULL value (for example, SRVBIT or SRVINT1), then the function ignores the cbLen parameter. cbLen=0 indicates zero-length data, and fNull must be set to FALSE.
fNull set this to TRUE if the return parameter needs to be set to NULL, and cbLen must be 0 or the function will fail. In all other cases fNull=FALSE.
If successful, the function returns SUCCEED. If the return value is FAIL, then the call failed. Everything is simple and clear
Now we know enough to write our first extended stored procedure, which will return a value through the parameter passed to it. Let, according to established tradition, this be the string Hello world! A debug version of the example can be downloaded here.
#include
#define XP_NOERROR 0
#define XP_ERROR 1
#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1
void printError(SRV_PROC*, CHAR*);
#ifdef __cplusplus
extern "C" (
#endif
SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);
#ifdef __cplusplus
}
#endif
SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = "Hello World!";
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;
/* Determining the number of transferred to the extended storage
parameter procedure */
if (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, "Invalid number of parameters!");
return (XP_ERROR);
}
/* Obtaining information about the data type and length of the passed parameters */
if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == FAIL)
{
printError(pSrvProc,
"Unable to obtain information about input parameters...");
return (XP_ERROR);
}
/* Check if the passed OUTPUT parameter is a parameter */
if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
{
printError(pSrvProc,
"The passed parameter is not an OUTPUT parameter!");
return (XP_ERROR);
}
/* Check the data type of the passed parameter */
if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError (pSrvProc, "The type of the passed parameter is not correct!");
return (XP_ERROR);
}
/* Make sure the passed parameter is long enough to store the returned string */
if (cbMaxLen< strlen(szText))
{
printError(pSrvProc,
"The parameter passed is not of sufficient length to store the n returned string!");
return (XP_ERROR);
}
/* Set the value of the OUTPUT parameter */
if (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError(pSrvProc,
"I can't set the value of the OUTPUT parameter...");
return (XP_ERROR);
}
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
return (XP_NOERROR);
}
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);
Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}
The functions srv_sendmsg and srv_senddone remained unconsidered. The srv_sendmsg function is used to send messages to the client. Here is its prototype:
int srv_sendmsg (
SRV_PROC * srvproc,
int msgtype,
DBINT msgnum,
DBTINYINT class
DBTINYINT state,
DBCHAR * rpcname,
int rpcnamelen,
DBUSMALLINT linenum,
DBCHAR *message,
int msglen
);
msgnum message number;
class- the severity of the error that occurred. Information messages have a severity value less than or equal to 10;
state The error status number for the current message. This parameter provides information about the context of the error that occurred. Valid values range from 0 to 127;
rpcname is currently unused;
rpcnamelen - currently not used;
linenum here you can specify the line number source code. Based on this value, it will subsequently be easy to determine where the error occurred. If you do not want to use this feature, then set linenum to 0;
message pointer to a string sent to the client;
msglen specifies the length in bytes of the message string. If this string ends with a null character, then the value of this parameter can be set to SRV_NULLTERM.
Return values:
- if successful SUCCEED
- if FAIL fails.
During operation, the extended stored procedure must regularly report its status to the client application, i.e. send messages about completed actions. This is what the srv_senddone function is designed for:
int srv_senddone(
SRV_PROC * srvproc,
DBUSMALLINT status,
DBUSMALLINT info,
DBINT count
);
status status flag. The value of this parameter can be set using the logical operators AND and OR to combine the constants given in the table:
Status flag Description
SRV_DONE_FINAL The current result set is final;
SRV_DONE_MORE The current result set is not final; the next batch of data should be expected;
SRV_DONE_COUNT The count parameter contains a valid value
SRV_DONE_ERROR Used to notify when errors occur and terminate immediately.
into reserved, must be set to 0.
count is the number of result sets sent to the client. If the status flag is set to SRV_DONE_COUNT, then count must contain the correct number of recordset sent to the client.
Return values:
- if successful SUCCEED
- if FAIL fails.
Installing extended stored procedures on MS SQL Server 2000
1.Copy the dll library with the extended stored procedure to the binn directory on the machine with MS SQL Server installed. My path is as follows: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2. Register the extended stored procedure on the server by executing the following script:
USE Master
EXECUTE SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll
Test xp_helloworld by running the following script:
DECLARE @Param varchar(33)
EXECUTE xp_helloworld @Param OUTPUT
SELECT @Param AS OUTPUT_Param
Conclusion
This concludes the first part of my article. Now I'm sure you're ready to handle our terms of reference at 100%. In the next article you will learn:
- Data types defined in the ODS API;
- Features of debugging extended stored procedures;
- How to create recordsets and transfer them to the client application;
- We will partially consider the Active Directory Network Manegment API functions necessary to obtain a list of domain users;
- We will create a finished project (we will implement our technical specifications)
I hope - see you soon!
PS: download example files for the article for Studio 7.0
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.
Transact-SQL
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.
Transact-SQL
SELECT * FROM dbo.MyTable --Doing it this way is good -- Instead of SELECT * FROM MyTable --And doing it this way is bad --Calling the EXEC procedure dbo.MyProc --Good again --Instead of EXEC MyProc --Bad!
Don't 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 expenses and even an incorrect result if a procedure with the same name as yours is found in its database.
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.
- Study operators describing stored procedures and the principles of passing their input and output parameters.
- Learn how to create and debug stored procedures on MS SQL Server 2000.
- Develop five basic stored procedures for the Library training database.
- Prepare a report on the work done in electronic form.
1. Understanding Stored Procedures
Stored Procedure this is a set of commands stored on the server and executed as a single unit. Stored procedures are a mechanism by which you can create routines that run on the server and are controlled by its processes. Such routines can be invoked by the application that calls them. They can also be caused by data integrity rules or triggers.
Stored procedures can return values. The procedure can compare user-entered values with information preset in the system. Stored procedures take advantage of powerful SQL Server hardware solutions. They are database-centric and work closely with the SQL Server optimizer. This allows you to get high performance when processing data.
You can pass values to stored procedures and receive results from them, not necessarily related to the worksheet. A stored procedure can compute results as it runs.
There are two types of stored procedures: ordinary And extended. Regular stored procedures are a set of Transact-SQL commands, while extended stored procedures are represented as dynamic-link libraries (DLLs). Such procedures, unlike ordinary ones, have the prefix xp_. The server has a standard set of extended procedures, but users can write their own procedures in any programming language. The main thing is to use the programming interface SQL Server Open Data Services API. Extended stored procedures can only reside in the Master database.
Regular stored procedures can also be divided into two types: systemic And custom. System procedures these are standard procedures used to operate the server; custom any procedures created by the user.
1.1. Benefits of Stored Procedures
In the most general case, stored procedures have the following advantages:
- High performance. Is the result of the location of stored procedures on the server. The server, as a rule, is a more powerful machine, so the execution time of the procedure on the server is significantly less than on workstation. Additionally, the database information and the stored procedure reside on the same system, so there is little time spent transferring records over the network. Stored procedures have direct access to databases, which makes working with information very fast.
- The advantage of developing a system in a client-server architecture. It consists in the possibility of separately creating client and server software. This advantage is key in development and can significantly reduce the time required to complete a project. The code running on the server can be developed separately from the client-side code. In this case, server-side components can be shared with client-side components.
- Security level. Stored procedures can act as a security enhancement tool. You can create stored procedures that perform add, edit, delete, and list display operations, giving you control over every aspect of information access.
- Strengthening server rules that work with data. This is one of the most important reasons application of an intelligent database engine. Stored procedures allow you to apply rules and other logic that help control information entered into the system.
Although SQL is defined as a non-procedural language, SQL Server uses keywords related to managing the flow of procedures. Such keywords are used to create procedures that can be saved for later execution. Stored procedures can be used instead of programs created using standard programming languages (for example, C or Visual Basic) and performing operations in the database SQL data Server.
Stored procedures are compiled the first time they are executed and stored in a system table in the current database. They are optimized when compiled. This selects the best way to access table information. This optimization takes into account the actual position of the data in the table, available indexes, table load, etc.
Compiled stored procedures can significantly improve system performance. It is worth noting, however, that data statistics from the time a procedure is created to the time it is executed may become out of date, and indexes may become ineffective. Although you can update the statistics and add new, more efficient indexes, the execution plan for the procedure has already been written, that is, the procedure has been compiled, and as a result the way you access the data may no longer be efficient. Therefore, it is possible to recompile procedures each time they are called.
On the other hand, recompilation will take time each time. Therefore, the question of the effectiveness of recompiling a procedure or drawing up a plan for its execution at once is quite delicate and should be considered for each specific case separately.
Stored procedures can be executed either on the local machine or on a remote SQL Server system. This makes it possible to activate processes on other machines and work not only with local databases data, but also with information on several servers.
Applications written in a high-level language, such as C or Visual Basic .NET, can also call stored procedures, providing optimal load balancing between the client-side and SQL server software.
1.2. Creating Stored Procedures
To create a stored procedure, use the Create Procedure statement. The stored procedure name can be up to 128 characters long, including the # and ## characters. Procedure definition syntax:
CREATE PROC procedure_name [; number]
[(@data_type parameter) [= default_value] ] [,...n]
AS
<Инструкции_SQL>
Let's look at the parameters of this command:
- Procedure_name procedure name; must satisfy the rules for identifiers: its length cannot exceed 128 characters; for local temporary procedures the # sign is used before the name, and for global temporary procedures ##;
- Number An optional integer used to group multiple procedures under one name;
- @parameter data_type a list of procedure parameter names indicating the corresponding data type for each; There can be up to 2100 such parameters. NULL can be passed as a parameter value. All data types can be used except for the text, ntext and image types. You can use the Cursor data type as an output parameter (the OUTPUT or VARYING keyword). Parameters with the Cursor data type can only be output parameters;
- VARYING keyword that specifies that the result set is used as the output parameter (used only for the Cursor type);
- OUTPUT indicates that the specified parameter can be used as an output;
- default_value used when a parameter is omitted when calling a procedure; must be a constant and can include wildcard characters (%, _, [, ], ^) and a NULL value;
- WITH RECOMPILE keywords indicating that SQL Server will not write the procedure plan to the cache, but will create it each time it is executed;
- WITH ENCRYPTION keywords indicating that SQL Server will encrypt the procedure before writing it to the Syscomments system table. To make the text of encrypted procedures impossible to recover, it is necessary to remove the corresponding tuples from the syscomments table after encryption;
- FOR REPLICATION keywords indicating that this procedure is created only for replication. This option is incompatible with the WITH RECOMPILE keywords;
- AS start of definition of procedure text;
- <Инструкции_SQL>set of valid SQL statements, limited only by the maximum size of the stored procedure 128 KB. The following statements are invalid: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DISK RESIZE, DROP DATA BASE DROP DEFAULT, DROP PROCEDURE, DROP RULE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATISTICS.
Let's look at an example of a stored procedure. Let's develop a stored procedure that counts and displays the number of copies of books that are currently in the library:
CREATE Procedure Count_Ex1
- procedure for counting the number of copies of books,
- currently in the library,
- and not in the hands of readers
As
-- set a temporary local variable
Declare @N int
Select @N = count(*) from Exemplar Where Yes_No = "1"
Select @N
GO
Since a stored procedure is a full-fledged component of the database, then, as you already understood, you can create a new procedure only for the current database. When working in SQL Server Query Analyzer, setting the current database is done with a Use statement followed by the name of the database where the stored procedure is to be created. You can also select the current database using the drop-down list.
After a stored procedure is created on the system, SQL Server compiles it and checks the routines that are executed. If any problems arise, the procedure is rejected. Errors must be corrected before retransmission.
SQL Server 2000 uses delayed name resolution name resolution), so if the stored procedure contains a call to another procedure that has not yet been implemented, a warning is printed, but the call to the non-existent procedure is preserved.
If you leave a call to an uninstalled stored procedure on the system, the user will receive an error message when they try to execute it.
You can also create a stored procedure with using SQL Server Enterprise Manager:
In order to check the functionality of the created stored procedure, you need to go to Query Analyzer and run the procedure for execution by the operator EXEC<имя процедуры> . The results of running the procedure we created are shown in Fig. 4.
Rice. 4. Running a stored procedure in Query Analyzer
Rice. 5. The result of executing the procedure without the display operator
1.3. Stored Procedure Parameters
Stored procedures are a very powerful tool, but maximum efficiency can only be achieved by making them dynamic. The developer must be able to pass the stored procedure values with which it will work, that is, parameters. Below are the basic principles for using parameters in stored procedures.
- You can define one or more parameters for a procedure.
- Parameters are used as named places to store data, just like variables in programming languages such as C, Visual Basic .NET.
- The parameter name must be preceded by the @ symbol.
- Parameter names are local to the procedure where they are defined.
- Parameters are used to pass information to a procedure when it is executed. They'll go crazy command line after the procedure name.
- If a procedure has several parameters, they are separated by commas.
- To determine the type of information passed as a parameter, system or user data types are used.
Below is the definition of a procedure that has one input parameter. Let's change the previous task and count not all copies of books, but only copies of a specific book. Our books are uniquely identified by a unique ISBN, so we will pass this parameter to the procedure. In this case, the text of the stored procedure will change and will look like this:
Create Procedure Count_Ex(@ISBN varchar(14))
As
Declare @N int
Select @N
GO
When we launch this procedure for execution, we must pass it the value of the input parameter (Fig. 6).
Rice. 6. Starting a procedure with passing a parameter
To create multiple versions of the same procedure that have the same name, follow the base name with a semicolon and an integer. How to do this is shown in the following example, which describes how to create two procedures with the same name, but with different numbers versions (1 and 2). The number is used to control which version of this procedure is being executed. If no version number is specified, the first version of the procedure is executed. This option is not shown in the previous example, but is still available for your application.
Both procedures use a print statement to print a message identifying the version. The first version counts the number of free copies, and the second the number of copies on hand for a given book.
The text of both versions of the procedures is given below:
CREATE Procedure Count_Ex_all; 1
(@ISBN varchar(14))
-- procedure for counting free copies of a given book
As
Declare @N int
Select @N = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
Select @N
--
GO
--
CREATE Procedure Count_Ex_all; 2
(@ISBN varchar(14))
-- procedure for counting free copies of a given book
As
Declare @N1 int
Select @N1 = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "0"
Select @N1
GO
The results of performing the procedure with different versions are shown in Fig. 7.
Rice. 7. Launch results different versions the same stored procedure
When writing multiple versions, keep the following restrictions in mind: Since all versions of a procedure are compiled together, all local variables are considered shared. Therefore, if this is required by the processing algorithm, it is necessary to use different names of internal variables, which is what we did by calling the variable @N in the second procedure with the name @N1.
The procedures we wrote do not return a single parameter, they simply display the resulting number on the screen. However, most often we need to get a parameter for further processing. There are several ways to return parameters from a stored procedure. The simplest one is to use the RETURN operator. This operator will allow you to return one numeric value. But we must specify the variable name or expression that is assigned to the return parameter. The following are the values returned by the RETURN statement and are reserved by the system:
| Code | Meaning |
| 0 | Everything is fine |
| 1 | Object not found |
| 2 | Data type error |
| 3 | The process fell victim to a deadlock |
| 4 | Access error |
| 5 | Syntax error |
| 6 | Some error |
| 7 | Error with resources (no space) |
| 8 | A recoverable internal error has occurred |
| 9 | System limit reached |
| 10 | Incorrigible violation of internal integrity |
| 11 | The same |
| 12 | Table or index destruction |
| 13 | Database destruction |
| 14 | Hardware error |
Thus, in order not to contradict the system, we can only return positive integers through this parameter.
For example, we can change the text of the previously written stored procedure Count_ex as follows:
Create Procedure Count_Ex2(@ISBN varchar(14))
As
Declare @N int
Select @N = count(*) from Exemplar
Where ISBN = @ISBN and YES_NO = "1"
-- return the value of the @N variable,
-- if the value of the variable is not defined, return 0
Return Coalesce(@N, 0)
GO
Now we can get the value of the @N variable and use it for further processing. In this case, the return value is assigned to the stored procedure itself, and in order to parse it, you can use the following stored procedure call statement format:
Exec<переменная> = <имя_процедуры> <значение_входных_параметров>
An example of calling our procedure is shown in Fig. 8.
Rice. 8. Passing the return value of a stored procedure to a local variable
Stored procedure input parameters can use a default value. This value will be used if the parameter value was not specified when calling the procedure.
The default value is specified using an equal sign after the description of the input parameter and its type. Consider a stored procedure that counts the number of copies of books of a given year of publication. The default release year is 2006.
CREATE PROCEDURE ex_books_now(@year int = 2006)
-- counting the number of copies of books of a given year of publication
AS
Declare @N_books int
select @N_books = count(*) from books, exemplar
where Books.ISBN = exemplar.ISBN and YEARIZD = @year
return coalesce(@N_books, 0)
GO
In Fig. Figure 9 shows an example of calling this procedure with and without specifying an input parameter.
Rice. 9. Calling a stored procedure with and without a parameter
All of the above examples of using parameters in stored procedures provided only input parameters. However, the parameters may also be output. This means that the value of the parameter, after the procedure completes, will be passed on to the person who called the procedure (another procedure, trigger, batch of commands, etc.). Naturally, in order to receive an output parameter, when calling, you should specify not a constant, but a variable as the actual parameter.
Note that defining a parameter as an output parameter in a procedure does not obligate you to use it as such. That is, if you specify a constant as the actual parameter, then no error will occur and it will be used as a normal input parameter.
To indicate that a parameter is an output, the OUTPUT statement is used. This keyword is written after the parameter description. When describing the parameters of stored procedures, it is advisable to specify the values of the output parameters after the input ones.
Let's look at an example of using output parameters. Let's write a stored procedure that, for a given book, counts the total number of its copies in the library and the number of free copies. We won't be able to use the RETURN statement here because it only returns one value, so we need to define the output parameters here. The stored procedure text might look like this:
CREATE Procedure Count_books_all
(@ISBN varchar(14), @all int output, @free int output)
-- procedure for counting the total number of copies of a given book
-- and the number of free copies
As
-- counting the total number of copies
Select @all = count(*) from Exemplar Where ISBN = @ISBN
Select @free = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
GO
An example of this procedure is shown in Fig. 10.
Rice. 10. Testing a stored procedure with output parameters
As mentioned earlier, in order to obtain the values of output parameters for analysis, we must set them to variables, and these variables must be described by the Declare operator. The last output statement allowed us to simply print the resulting values to the screen.
Procedure parameters can even be variables of type Cursor . To do this, the variable must be described as a special data type VARYING, without binding to the standard ones system types data. In addition, it must be indicated that this is a variable of type Cursor .
Let's write a simple procedure that displays a list of books in our library. Moreover, if there are no more than three books, then we display their names within the procedure itself, and if the list of books exceeds the specified number, then we pass them as a cursor to the calling program or module.
The text of the procedure looks like this:
CREATE PROCEDURE GET3TITLES
(@MYCURSOR CURSOR VARYING OUTPUT)
-- procedure for printing book titles with a cursor
AS
-- define local type variable Cursor in procedure
SET @MYCURSOR = CURSOR
FOR SELECT DISTINCT TITLE
FROM BOOKS
-- open the cursor
OPEN @MYCURSOR
-- describe internal local variables
DECLARE @TITLE VARCHAR(80), @CNT INT
--- set the initial state of the book counter
SET @CNT = 0
-- go to the first line of the cursor
-- while there are cursor lines,
-- that is, while the transition to new line correct
WHILE (@@FETCH_STATUS = 0) AND (@CNT<= 2) BEGIN
PRINT @TITLE
FETCH NEXT FROM @MYCURSOR INTO @TITLE
-- change the state of the book counter
SET @CNT = @CNT + 1
END
IF @CNT = 0 PRINT "NO MATCHING BOOKS"
GO
An example of calling this stored procedure is shown in Fig. 11.
In the calling procedure, the cursor must be declared as a local variable. Then we called our procedure and passed it the name of a local variable of type Cursor. The procedure started working and displayed the first three names on the screen, and then transferred control to the calling procedure, and it continued processing the cursor. To do this, she organized a While loop using the global variable @@FETCH_STATUS, which monitors the state of the cursor, and then in the loop displayed all other lines of the cursor.
In the output window we see increased spacing between the first three lines and subsequent titles. This interval just shows that control has been transferred to an external program.
Note that the @TITLE variable, being local to the procedure, will be destroyed when it terminates, so it is declared again in the block that calls the procedure. The creation and opening of the cursor in this example occurs in a procedure, and the closing, destruction, and additional processing are performed in the command block in which the procedure is called.
The easiest way to view the text of a procedure, change or delete it is using the Enterprise Manager graphical interface. But you can also do this using special Transact-SQL system stored procedures. In Transact-SQL, you can view a procedure definition using the sp_helptext system procedure, and you can use the sp_help system procedure to display control information about the procedure. The sp_helptext and sp_help system procedures are also used to view database objects such as tables, rules, and default settings.
Information about all versions of one procedure, regardless of the number, is displayed immediately. Deleting different versions of the same stored procedure also occurs simultaneously. The following example shows how version 1 and version 2 definitions of Count_Ex_all are printed when its name is specified as a parameter to the sp_helptext system procedure (Figure 12).
Rice. 12. Viewing stored procedure text using a system stored procedure
The system procedure SP_HELP displays the characteristics and parameters of the created procedure in the following form:
|
Created_datetime | |||||
|
2006-12-06 23:15:01.217 | |||||
|
Scale | Param_order Collation | ||||
|
NULL | 1 | Cyrillic_General_CI_AS | |||
|
0 | 2 | NULL | |||
|
0 | 3 | NULL | |||
Try to decipher these parameters yourself. What are they talking about?
1.4. Compiling a Stored Procedure
The advantage of using stored procedures to execute a set of Transact-SQL statements is that they are compiled the first time they are executed. During the compilation process, Transact-SQL statements are converted from their original symbolic representation into executable form. Any objects accessed in the procedure are also converted to an alternate representation. For example, table names are converted to object identifiers, and column names are converted to column identifiers.
The execution plan is created in the same way as for executing a single Transact-SQL statement. This plan contains, for example, indexes used to read rows from the tables accessed by the procedure. The procedure's execution plan is stored in the cache and used each time it is called.
Note: The procedure cache can be sized to contain most or all of the procedures available for execution. This will save the time required to regenerate the procedure plan.
1.5. Automatic recompilation
Typically the execution plan is located in the procedure cache. This allows you to increase the performance of its execution. However, under some circumstances the procedure is automatically recompiled.
- The procedure is always recompiled when SQL Server starts. This usually occurs after the operating system is restarted and the first time the procedure is executed after creation.
- A procedure's execution plan is always automatically recompiled if the index on the table accessed by the procedure is dropped. Because the current plan accesses an index that no longer exists to read table rows, a new execution plan must be created. Procedure queries will only be executed if it is updated.
- Compilation of the execution plan also occurs if another user is currently working with this plan located in the cache. An individual copy of the execution plan is created for the second user. If the first copy of the plan were not busy, there would be no need to create a second copy. When a user completes a procedure, the execution plan is available in the cache to another user who has the appropriate access permission.
- A procedure is automatically recompiled if it is deleted and recreated. Because the new procedure may differ from the old version, any copies of the execution plan in the cache are removed and the plan is recompiled.
SQL Server strives to optimize stored procedures by caching the most heavily used procedures. Therefore, the old execution plan loaded into the cache can be used instead of the new plan. To prevent this problem, you should delete and recreate the stored procedure, or stop and restart SQL Server. This will clear the procedure cache and eliminate the possibility of working with an old execution plan.
The procedure can also be created with the WITH RECOMPILE option. In this case, it will be automatically recompiled every time it is executed. The WITH RECOMPILE option should be used in cases where the procedure accesses very dynamic tables whose rows are frequently added, deleted, or updated, since this causes significant changes to the indexes defined on the tables.
If procedures are not automatically recompiled, you can force them to do so. For example, if the statistics used to determine whether an index can be used in a given query are updated, or if a new index is created, a force recompilation must be performed. To force a recompilation, use the WITH RECOMPILE clause in the EXECUTE statement:
EXECUTE procedure_name;
AS
<инструкции Transact-SQL>
WITH RECOMPILE
If the procedure operates with parameters that control the order in which it is executed, you should use the WITH RECOMPILE option. If the parameters of a stored procedure can determine the best path for executing it, it is recommended that you create an execution plan as you run it, rather than creating one the first time you call the procedure for use in all subsequent calls.
Note: Sometimes it can be difficult to determine whether to use the WITH RECOMPILE option when creating a procedure or not. If in doubt, it is best not to use this option, since recompiling the procedure each time it is executed will waste very valuable CPU time. If you need to recompile a stored procedure in the future, you can do so by adding a WITH RECOMPILE clause to the EXECUTE statement.
You cannot use the WITH RECOMPILE option in a CREATE PROCEDURE statement that contains a FOR REPLICATION option. Use this option to create a procedure that runs during the replication process.
1.6. Nesting of stored procedures
Stored procedures can call other stored procedures, but there is a limit on the level of nesting. The maximum nesting level is 32. The current nesting level can be determined using the @@NESTLEVEL global variable.
2. User Defined Functions (UDF)
MS SQL SERVER 2000 has many predefined functions that allow you to perform various actions. However, there may always be a need to use some specific functions. To do this, starting with version 8.0 (2000), it became possible to describe user defined functions (UDF) and store them as a full-fledged database object, along with stored procedures, views, etc.
The convenience of using user-defined functions is obvious. Unlike stored procedures, functions can be embedded directly in a SELECT statement, and can be used both to retrieve specific values (in the SELECT clause) and as a data source (in the FROM clause).
When using UDFs as data sources, their advantage over views is that UDFs, unlike views, can have input parameters that can be used to influence the outcome of the function.
User-defined functions can be of three types: scalar functions, inline functions And multi-statement functions that return a table result. Let's take a closer look at all these types of functions.
2.1. Scalar functions
Scalar functions return a single scalar result. This result can be any of the types described above, except for the text, ntext, image, and timestamp types. This is the simplest type of function. Its syntax is as follows:
RETURNS scalar_data_type
BEGIN
body_function
RETURN scalar_expression
END
- The ENCRYPTION parameter has already been described in the section on stored procedures;
- SCHEMABINDING binds a function to a schema. This means that you cannot delete the tables or views that the function is based on without deleting or modifying the function itself. You also cannot change the structure of these tables if the part being changed is used by a function. Thus, this option allows you to eliminate situations where the function uses some tables or views, and someone, without knowing about it, deleted or changed them;
- RETURNS scalar_data_type describes the data type that the function returns;
- scalar_expression an expression that directly returns the result of a function. It must be of the same type as the one described after RETURNS;
- function_body set of Transact-SQL instructions.
Let's look at examples of using scalar functions.
Create a function that will select the smallest of two integers supplied as input as parameters.
Let the function look like this:
CREATE FUNCTION min_num(@a INT, @b INT)
RETURNS INT
BEGIN
DECLARE @c INT
IF @a< @b SET @c = @a
ELSE SET @c = @b
RETURN @c
END
Let's now execute this function:
SELECT dbo.min_num(4, 7)
As a result, we get the value 4.
You can use this function to find the smallest of the table column values:
SELECT min_lvl, max_lvl, min_num(min_lvl, max_lvl)
FROM Jobs
Let's create a function that will receive a datetime type parameter as input and return the date and time corresponding to the beginning of the specified day. For example, if the input parameter is 09.20.03 13:31, then the result will be 09.20.03 00:00.
CREATE FUNCTION dbo.daybegin(@dat DATETIME)
RETURNS smalldatetime AS
BEGIN
RETURN CONVERT(datetime, FLOOR(convert(FLOAT, @dat)))
END
Here the CONVERT function performs type conversion. First, the datetime type is cast to FLOAT. With this reduction, the integer part is the number of days counting from January 1, 1900, and the fractional part is the time. Next, it is rounded to a smaller integer using the FLOOR function and converted to a date-time type.
Let's check the function:
SELECT dbo.daybegin(GETDATE())
Here GETDATE() is a function that returns the current date and time.
Previous functions used only input parameters in their calculations. However, you can also use data stored in a database.
Let's create a function that will take two dates as parameters: the beginning and end of a time interval and calculate the total sales revenue for this interval. The sale date and quantity will be taken from the Sales table, and the prices for the titles being sold will be taken from the Titles table.
CREATE FUNCTION dbo.SumSales(@datebegin DATETIME, @dateend DATETIME)
RETURNS Money
AS
BEGIN
DECLARE @Sum Money
SELECT @Sum = sum(t.price * s.qty)
RETURN @Sum
END
2.2. Inline functions
This type of function returns as a result not a scalar value, but a table, or rather a data set. This can be very convenient in cases where the same type of subquery is often executed in different procedures, triggers, etc. Then, instead of writing this query everywhere, you can create a function and use it in the future.
Functions of this type are even more useful in cases where you want the returned table to depend on input parameters. As you know, views cannot have parameters, so only inline functions can solve this kind of problem.
The peculiarity of inline functions is that they can contain only one request in their body. Thus, functions of this type are very similar to views, but can additionally have input parameters. Inline function syntax:
CREATE FUNCTION [owner.]function_name
([(@parameter_name scalar_data_type [= default_value]) [, n]])
RETURNS TABLE
RETURN [(<запрос>)]
The function definition states that it will return a table;<запрос>this is the request, the result of which will be the result of the function.
Let's write a function similar to the scalar function from the last example, but returning not only the summing result, but also sales rows, including the date of sale, title of the book, price, number of pieces and sale amount. Only those sales that fall within a given time period should be selected. Let's encrypt the text of the function so that other users can use it, but cannot read and correct it:
CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
RETURNS TABLE
WITH ENCRYPTION
AS
RETURN (
SELECT t.title, t.price, s.qty, ord_date, t.price * s.qty as stoim
FROM Titles t JOIN Sales s ON t.title_Id = s.Title_ID
WHERE ord_date BETWEEN @datebegin and @dateend
)
Now let's call this function. As already mentioned, it can only be called in the FROM clause of the SELECT statement:
SELECT * FROM Sales_Period("09/01/94", "09/13/94")
2.3. Multistatement functions that return a table result
The first type of functions considered allowed the use of as many Transact-SQL statements as desired, but returned only a scalar result. The second type of function could return tables, but its body represents only one query. Multi-statement functions that return a table result allow you to combine the properties of the first two functions, that is, they can contain many Transact-SQL statements in the body and return a table as a result. Multistatement function syntax:
CREATE FUNCTION [owner.]function_name
([(@parameter_name scalar_data_type [= default_value]) [,... n]])
RETURNS @result variable_name TABLE
<описание_таблицы>
BEGIN
<тело_функции>
RETURN
END
- TABLE<описание_таблицы> describes the structure of the returned table;
- <описание_таблицы> contains a list of columns and constraints.
Now let's look at an example that can only be done using functions of this type.
Let there be a tree of directories and files contained in them. Let this entire structure be described in the database in the form of tables (Fig. 13). Essentially, here we have a hierarchical structure for directories, so the diagram shows the relationship of the Folders table to itself.
Rice. 13. Database structure to describe the hierarchy of files and directories
Now let's write a function that will take a directory identifier as input and output all the files that are stored in it and in all directories down the hierarchy. For example, if the directories Faculty1, Faculty2, etc. are created in the Institute directory, they contain department directories, and each directory contains files, then when we specify the Institute directory identifier as a parameter to our function, a list of all files for all these directories. For each file, the name, size and creation date should be displayed.
The problem cannot be solved using an inline function, since SQL is not designed to perform hierarchical queries, so one SQL query is not enough. A scalar function cannot be used either, since the result must be a table. This is where a multi-statement function that returns a table will come to our aid:
CREATE FUNCTION dbo.GetFiles(@Folder_ID int)
RETURNS @files TABLE(Name VARCHAR(100), Date_Create DATETIME, FileSize INT) AS
BEGIN
DECLARE @tmp TABLE(Folder_Id int)
DECLARE @Cnt INT
INSERT INTO @tmp values(@Folder_ID)
SET @Cnt = 1
WHILE @Cnt<>0 BEGIN
INSERT INTO @tmp SELECT Folder_Id
FROM Folders f JOIN @tmp t ON f.parent=t.Folder_ID
WHERE F.id NOT IN(SELECT Folder_ID FROM @tmp)
SET @Cnt = @@ROWCOUNT
END
INSERT INTO @Files(Name, Date_Create, FileSize)
SELECT F.Name, F.Date_Create, F.FileSize
FROM Files f JOIN Folders Fl on f.Folder_id = Fl.id
JOIN @tmp t on Fl.id = t.Folder_Id
RETURN
END
Here, in a loop, all subdirectories at all nesting levels are added to the @tmp variable until there are no more subdirectories left. The @Files result variable then records all the necessary attributes of the files located in the directories listed in the @tmp variable.
Tasks for independent work
You must create and debug five stored procedures from the following required list:
Procedure 1. Increasing the deadline for submitting copies of the book by a week if the current due date lies within the range from three days before the current date to three days after the current date.
Procedure 2. Counting the number of free copies of a given book.
Procedure 3. Checking the existence of a reader with a given last name and date of birth.
Procedure 4. Entering a new reader, checking his existence in the database and determining his new library card number.
Procedure 5. Calculation of fines in monetary terms for debtor readers.
Brief description of procedures
Procedure 1. Increasing the deadline for delivery of books
For each record in the Exemplar table, it is checked whether the book's due date falls within the specified time interval. If it does, the return date for the book is extended by a week. When performing the procedure, you must use the function for working with dates:
DateAdd(day,<число добавляемых дней>, <начальная дата>)
Procedure 2. Counting the number of free copies of a given book
The input parameter of the procedure is ISBN the unique cipher of the book. The procedure returns 0 (zero) if all copies of this book are in the hands of readers. The procedure returns the value N, equal to the number of copies of the book that are currently in the hands of readers.
If a book with the given ISBN is not in the library, then the procedure returns 100 (minus one hundred).
Procedure 3. Checking the existence of a reader with a given last name and date of birth
The procedure returns the library card number if a reader with such data exists, and 0 (zero) otherwise.
When comparing date of birth, you must use the Convert() conversion function to convert the date of birth, a Varchar(8) character variable used as an input parameter to the procedure, into datatime, which is used in the Readers table. Otherwise, the comparison operation when searching for a given reader will not work.
Procedure 4: Entering a New Reader
The procedure has five input and three output parameters.
Input parameters:
- Full name with initials;
- Address;
- Date of birth;
- Home phone;
- The phone is working.
Output parameters:
- Library card number;
- An indication of whether the reader was previously registered in the library (0 was not, 1 was);
- The number of books a reader owns.
Procedure 5. Calculation of fines in monetary terms for debtor readers
The procedure works with a cursor that contains a list of library card numbers of all debtors. During the work process, a global temporary table ##DOLG should be created, in which for each debtor his total debt in monetary terms for all books that he held longer than the return period will be entered. Cash compensation is calculated at 0.5% of the price per book per day of delay.
Work order
- copies of screens (screenshots) confirming changes made to the databases;
- the contents of database tables that are required to verify correct operation;
- text of the stored procedure with comments;
- the process of running a stored procedure and outputting the results of the work.
Additional tasks
The following additional stored procedures are for individual jobs.
Procedure 6. Counting the number of books on a given subject area that are currently available in the library in at least one copy. The subject area is passed as an input parameter.
Procedure 7. Entering a new book indicating the number of copies. When entering copies of a new book, be sure to enter their correct accession numbers. Think about how you can do this. As a reminder, you have the Max and Min functions, which let you find the maximum or minimum value of any numeric attribute using a Select query.
Procedure 8. Formation of a table with a list of debtor readers, that is, those who should have returned books to the library, but have not yet returned them. In the resulting table, each debtor reader should appear only once, regardless of how many books he owes. In addition to your full name and library card number, you must indicate your address and telephone number in the resulting table.
Procedure 9. Search for a free copy using a given book title. If there is a free copy, the procedure returns the inventory number of the copy; if not, then the procedure returns a list of readers who have this book, indicating the date of return of the book and the reader's phone number.
Procedure 10. Displaying a list of readers who do not currently have any books in their hands. Please indicate your name and phone number in the list.
Procedure 11. Displaying a list of books indicating the number of copies of a given book in the library and the number of free copies at the moment.
Print version
In Microsoft SQL Server to implement and automate your own algorithms ( calculations) you can use stored procedures, so today we will talk about how they are created, modified and deleted.
But first, a little theory so that you understand what stored procedures are and why they are needed in T-SQL.
Note! For beginning programmers, I recommend the following useful materials on T-SQL:
- For a more detailed study of the T-SQL language, I also recommend reading the book - The T-SQL Programmer's Path. Tutorial on the Transact-SQL language;
- Professional online courses on T-SQL
What are stored procedures in T-SQL?
Stored procedures– these are database objects that contain an algorithm in the form of a set of SQL instructions. In other words, we can say that stored procedures are programs inside a database. Stored procedures are used to store reusable code on the server, for example, you wrote a certain algorithm, sequential calculation or multi-step SQL statement, and in order not to execute all the instructions included in this algorithm each time, you can format it as a stored procedure. At the same time, when you create a SQL procedure, the server compiles the code, and then, every time you run this SQL procedure, the server will not recompile it.
In order to run a stored procedure in SQL Server, you must write the EXECUTE command before its name; it is also possible to abbreviate this command as EXEC. Calling a stored procedure in a SELECT statement, for example, as a function will no longer work, i.e. procedures are launched separately.
In stored procedures, unlike functions, it is already possible to perform data modification operations such as: UNSERT, UPDATE, DELETE. You can also use SQL statements of almost any type in procedures, for example, CREATE TABLE to create tables or EXECUTE, i.e. calling other procedures. The exception is several types of instructions, such as: creating or changing functions, views, triggers, creating schemas and several other similar instructions, for example, you also cannot switch the database connection context (USE) in a stored procedure.
A stored procedure can have input parameters and output parameters, it can return tabular data, or it can return nothing, only execute the instructions contained in it.
Stored procedures are very useful, they help us automate or simplify many operations, for example, you constantly need to generate various complex analytical reports using pivot tables, i.e. PIVOT operator. To make it easier to formulate queries with this operator ( as you know, PIVOT's syntax is quite complex), You can write a procedure that will dynamically generate summary reports for you, for example, the material “Dynamic PIVOT in T-SQL” provides an example of implementing this feature in the form of a stored procedure.
Examples of working with stored procedures in Microsoft SQL Server
Source data for examples
All examples below will be run in Microsoft SQL Server 2016 Express. In order to demonstrate how stored procedures work with real data, we need this data, let's create it. For example, let's create a test table and add some records to it, let's say that it will be a table containing a list of products with their prices.
Instruction for creating a table CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instruction for adding data INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO --Select query SELECT * FROM TestTable
We have the data, now let's move on to creating stored procedures.
Creating a stored procedure in T-SQL - the CREATE PROCEDURE statement
Stored procedures are created using a statement CREATE PROCEDURE, after this instruction you must write the name of your procedure, then, if necessary, define the input and output parameters in parentheses. After this, you write the keyword AS and open the block of instructions with the keyword BEGIN, close this block with the word END. Inside this block, you write all the instructions that implement your algorithm or some kind of sequential calculation, in other words, you program in T-SQL.
For example, let's write a stored procedure that will add a new record, i.e. new product to our test table. To do this, we will define three input parameters: @CategoryId – product category identifier, @ProductName – product name and @Price – product price; this parameter will be optional, i.e. it will not be necessary to pass it to the procedure ( for example, we don’t know the price yet), for this purpose we will set a default value in its definition. These parameters are in the body of the procedure, i.e. in the BEGIN...END block can be used, just like regular variables ( As you know, variables are denoted by the @ sign). If you need to specify output parameters, then after the parameter name indicate the keyword OUTPUT ( or OUT for short).
In the BEGIN...END block we will write an instruction for adding data, as well as a SELECT instruction at the end of the procedure, so that the stored procedure will return us tabular data about the products in the specified category, taking into account the new, just added product. Also in this stored procedure, I added processing of the incoming parameter, namely, removing extra spaces at the beginning and end of the text string in order to eliminate situations where several spaces were accidentally entered.
Here is the code for this procedure ( I also commented on it).
Create a procedure CREATE PROCEDURE TestProcedure (--Input parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Removing extra spaces at the beginning and in end of the text line SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add a new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return the data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Running a stored procedure in T-SQL - EXECUTE command
You can run a stored procedure, as I already noted, using the EXECUTE or EXEC command. Incoming parameters are passed to procedures by simply listing them and specifying the appropriate values after the procedure name ( for output parameters you also need to specify the OUTPUT command). However, the names of the parameters may not be specified, but in this case it is necessary to follow the sequence of specifying the values, i.e. specify values in the order in which the input parameters are defined ( this also applies to output parameters).
Parameters that have default values do not need to be specified; these are the so-called optional parameters.
Here are a few different but equivalent ways to run stored procedures, specifically our test procedure.
1. Call the procedure without specifying the price EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Call the procedure indicating the price EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. Call the procedure without specifying the name of the parameters EXEC TestProcedure 1, "Test product 3", 400
Changing a stored procedure to T-SQL - ALTER PROCEDURE statement
You can make changes to the algorithm of the procedure using the instructions ALTER PROCEDURE. In other words, in order to change an already existing procedure, you just need to write ALTER PROCEDURE instead of CREATE PROCEDURE, and change everything else as necessary.
Let's say we need to make changes to our test procedure, say the @Price parameter, i.e. price, we will make it mandatory, for this we will remove the default value, and also imagine that we no longer need to obtain the resulting data set, for this we will simply remove the SELECT statement from the stored procedure.
We change the procedure ALTER PROCEDURE TestProcedure (--Incoming parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Removing extra spaces at the beginning and end of the text lines SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add a new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Deleting a stored procedure in T-SQL - DROP PROCEDURE statement
If necessary, you can delete the stored procedure; this is done using the instructions DROP PROCEDURE.
For example, let's delete the test procedure we created.
DROP PROCEDURE TestProcedure
When deleting stored procedures, it is worth remembering that if the procedure is referenced by other procedures or SQL statements, after it is deleted, they will fail with an error, since the procedure they reference no longer exists.
That’s all I have, I hope the material was interesting and useful to you, bye!
