21. Establish and manage pre-deputables
What pre-stored procedures?
Establish a pre-store program
Use T-SQL Management Pre-depreciation
Summary of this chapter
This chapter will learn Microsoft SQL Server pre-depiction procedures and how to use. First introduce the type of pre-stored program, then learn to establish and manage the pre-depreciation and definition parameters and variables of the user's custom. There are four ways to establish a pre-depreciation process. This chapter will describe how to use Transact-SQL (T-SQL), SQL Server Enterprise Manager, and establish the pre-stored program wizard, and the fourth method is to use SQL Distributed Management Objects (SQL-DMO) because This method is related to the application design, so it will not be discussed here. In the process of operation, you can find that the three ways to establish a pre-deployment program are used to use the T-SQL program code. The
What pre-stored procedures? The Stored Procedure is a collection of T-SQL statements, and SQL Server compiles statements in the collection into an execution planning unit. When the pre-depreciation process is executed, the compiled execution plan is stored in the program of the memory, and can be reused so that SQL Server does not have to repeat the analysis syntax correctly when each execution of the same task is correct. The programs of the pre-store and other programs are similar, and the incoming parameters can be accepted, and the value of the parameters is returned, or the message to successfully or fail. When the program is called, all statements in the program are executed. The pre-depreciation program will execute the T-SQL statement and related logic required for a task. Since the pre-depiction can be considered an execution unit, it can be repeatedly executed by different users, and even multiple applications can be across multiple applications. The pre-deployment has also provided a single control point for the job to ensure the correctness and consistency of the business rules. Your application ends can communicate with SQL Server through two ways: You can write an application to send T-SQL statements from the client to SQL Server, or establish a pre-depreciation program to store and execute on the server. If you transfer T-SQL from the client application to the server, the statement will be transmitted through the network and compile each time it is executed. If you use a pre-store, you can call the pre-depreciation from the application by a statement, and execute the pre-store. As mentioned earlier, SQL Server compiles the pre-depiction of the first execution and stores this execution plan in the memory. If you call the same execution plan again, you don't need to repeat the compilation. When a work is executed, a number of T-SQL statements are required, or some statements are often implemented, using the pre-depressed program does not need to perform a statement each time, it is necessary to pass the result through the network from the user end, thus Reduce the traffic of the network and the burden on the system.
The pre-depiction can also improve the performance performance through other methods. For example, the amount of data transmitted between the user and the servo can be reduced through the pre-depiction process, and the amount of data required for the user end is relatively reduced, so that the pre-depression program is performed on the server, it is a method of increasing performance. If it is determined in the internal test condition determination of the pre-stored program, the conditional statement can be applied in the pre-deposit program (such as the IF and the While Structure discussed in Chapter 2). This test logic will be executed on the server through the pre-depreciation, not necessarily written to the application, and the server does not have to return the result to the user end to perform test conditions. You can also call the pre-depicting program from the instruction code, batch, or using T-SQL statement (this chapter will be introduced later).
The pre-deposit procedure also provides a simple information inventory way for users. Users don't have to know the structure of the data sheet can access the database, in other words, they only need to perform the tasks required, without the need to enter the information sheet. The pre-depreciation program is to ensure business rules in this way. The pre-depreciation program can receive input parameters, use zone variables, and return information. The pre-depoxing program transmits the result of the SELECT statement, or the entire domain data indicator (GLOBAL CURSOR) is returned. In addition to using the whole domain information indicators, other techniques will have examples later in this chapter.
relevant information
For information on the banquet indicators, you can enter the "indicator" keyword in the "Line Book" search page, in the Transact-SQL Program Syntax Reference Description, select Declare Cursor, you can find the wireless domain information Relevant information on indicators.
The pre-save program is divided into three types: system pre-depreciation program, extension pre-deposit procedure, and simple user self-exemplification procedure. System Sotred Procedure is built by SQL Server, and has a word SP_ for managing SQL Server and Display Database and User Information, in Chapter 13; Extended Program (Extended) Sotred procedure is a dynamic linkage library (DLLS), SQL Server can be dynamically loaded and executed, usually using C or C language, including the headed XP_; Simple user self-deposit program is built by the user self-order execution Users specified tasks.
Description
When establishing a simple user's self-exemption program, SP_ is not used as a word, because when SQL Server encounters a pre-depression program with a header SP_, you will look for a pre-depreciation program in the Master database. For example, if in the MYDB database, name SP_MYPROC, SQL Server will find the pre-depreciation in the Master database (of course, you can not find) before you use it. The database is looking for. Therefore, the program is simpler named MyProc will be more efficient.
We will briefly introduce the extension of the program, and the key points of this chapter will be placed in the simple user's self-exemption procedure. Extended pre-deposited procedures can be used to expand the usability and ductility of the SQL Server environment, when executed, and other two types of pre-deposited programs can be used, that is, to extend the parameters. The pre-depreciation program can also be transferred back to the resulting set and status.
As mentioned earlier, the extension prediction procedure belongs to a dynamic linkage library (DLLS), and SQL Server can be dynamically loaded and executed, and executed directly in SQL SEVER address space. You can use the SQL SEVER OPEN DATA Services API to write programs.
The extension pre-depoxing program can be written outside the SQL Sever, and the extended pre-preservation program can be registered using the T-SQL command or registering the extension using the ENTERPRISE Manager using the ENTERPRISE Manager.
relevant information
In SQL Sever "Online Book", you can find an example of how to extend pre-depreciation procedures.
Establishing a Pre-Deprecation This section describes three ways to establish a pre-depreciation: T-SQL Create Procedure statement, Enterprise Manager, and establish a pre-depreciation.
The basic syntax of the CREATE Procedure statement is as follows:
Create Procedure [Procedure_name]
[{@Parameter_name Data_Type}] [= default] [OUTPUT] [, ..., n]
As T-SQL_STATEMENT (S)
First we create a simple pre-depreciation, this pre-depression will select (and back) the information in the three data lines in each of the ORDERS table, and the date in the ShippedDate data is later than the date of RequiredDate. It will return to the information. Note that the pre-depiction can only be built on the database, so you must use the USE statement to specify the database. Before establishing a pre-store, you must first determine if there is a replicated name existence, we must name the pre-stored program with unsaved names, or remove the already existing name and rename it. The T-SQL program code used to establish the program is as follows:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "latessments" and
TYPE = "P")
Drop Procedure Lateshipments
Go
Create Procedure Lateshipments
AS
SELECT RequiredDate,
ShippedDate,
Shippers.comPanyName
From Orders, Shippers
Where shippedDate> RequiredDate and
ORDERS.SHIPVIA = Shippers.shipperid
Go
Executing the above T-SQL program will establish a pre-store, to execute the pre-depression, as long as the name is called:
Lateshipments
Go
Lateshipments will return to 37 data. If the statement of the pre-depicting program is a batch of statements, and is not the first statement of the batch operation, you must use the Execute (abbreviated as an EXEC) keyword call, the use example is as follows:
SELECT getDate ()
Execute LateShipments
Go
If the call program is stated for the first statement of the batch, you can also use the Execute keyword without the unique statement of the batch operation. Using the parameters Next, let us add an input parameter in this pre-existing program to send parameters to the pre-depicting program when calling the pre-depreciation. To specify the input parameters in the pre-depreciation, please use the front word of the parameter name, such as @Parameter_name, and a pre-depreciation can specify up to 1024 parameters. In the example, the parameters of @ShipPername will be established. When performing a pre-preserved program, the name of the ship will only return to the information column of the ship company. The T-SQL statement is as follows:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "latessments" and
TYPE = "P")
Drop Procedure Lateshipments
Go
Create Procedure Lateshipments @ShipperName Char (40)
AS
SELECT RequiredDate,
ShippedDate,
Shippers.comPanyName
From Orders, Shippers
Where shippedDate> RequiredDate andorders.shipvia = shippers.shipperid and
Shippers.comPanyName = @shippername
Go
When you perform this pre-save, you must first provide an input parameter, otherwise the following error message is displayed:
Server: Information 201, Level 16, State 4, Program Lateshipments, Row 0
The program 'LATESHIPMENTS' is expected to use unpreated parameters '@shippername'.
If you want to pass the SPEEDY Express, please follow the required information, please do the following statements:
Use northwind
Go
Execute Lateshipments "Speedy Express"
Go
The pre-depreciation program will pass 12 data columns. You can also set a preset value for the parameter, which will apply to the case where there is no parameter when the call pre-preserved program is called. For example, we set the pre-depreciation parameter preset value to United Package, and its T-SQL pre-preserved program code is changed (change the CREATE Procedure line):
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "latessments" and
TYPE = "P")
Drop Procedure Lateshipments
Go
Create Procedure Lateshipments @shippername Char (40) = "United
Package "
AS
SELECT RequiredDate,
ShippedDate,
Shippers.comPanyName
From Orders, Shippers
Where shippedDate> RequiredDate and
ORDERS.SHIPVIA = Shippers.shipperid and
Shippers.comPanyName = @shippername
Go
If you do not provide parameters when executing Lateshipments, the pre-depreciation will use United Package to @ShipperName's preset value (and back 16 data columns). Even if the preset parameter has been determined, the input parameters can still be provided, and this parameter overwrites the preset value. If you want to pass the value to the call in the pre-store, use the Output keyword after the parameter name. To store the value in the variable for the call pre-depreciation, you can use the Output key when you call the pre-preserved program. Let us actually operate an example, first create a new pre-save program to choose a single price, @ product_id is the input parameters of ProductID, @ unit_price output parameters will return to the unit price, the area of the name @Price will be in the call program Declaration and use to store the passage. Below is the program code for establishing a getUnitprice pre-depicting program:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
WHERE Name = "getUnitprice" and
Type = "p") Drop Procedure GetUnitprice
Go
Create Procedure getUnitprice @prod_id int, @Unit_price mother Out-
PUT
AS
SELECT @Unit_price = Unitprice
From product
Where produter = @Prod_id
Go
You must declare the variable in the call program to use this variable in the pre-stored program call. For example, in the following program code, we will first declare @price variables and set their data type to Money (it must comply with Output parameter data, then do this):
Declare @Price Money
Execute getUnitprice 77, @Unit_price = @price output
Print Convert (VARCHAR (6), @PRICE)
Go
The Print statement is @Price pass-forward value 13.00. Note that we use the CONVERT statement to convert @price by the original Money data, convert to a VARCHAR data type, or convert this value as a string, a character data type, or in a non-direct manner into a word. To print (these are the print requirements of the Print state). Note that the variables of different names are used for Output in the pre-stored program and call program, so that you can easier you to find the location of the variable in the example and emphasize the name. You can also use variables to specify input values when performing a pre-depreciation, allowing the pre-depreciation program to receive values from the call program, then modify the value or utilize the value to perform some job, and then pass the new value back to the call. The operation method is to assign a value for a variation in the call program before performing the pre-depreciation program (or execute the query to insert the value in the variable), and then transfer the variable to the pre-program. Take a look at how to use zone variables in the pre-store. Using the zone variable Declare keyword in the pre-queer is used to establish a region variable. When establishing a zone variable, you must specify the area variable name and data type, and the name must be pre-word in the @ 字. One of the variables, the value will be set to NULL first. Region variables can be announced in batches, instruction codes (or call programs) or pre-depreciation. The variables in the pre-depreciation process are usually used to store the data values that the conditional statements are tested, or store the data values repayed back in the pre-store RETURN statement. Variables are often used as a counter. The variable range begins with the declaration of variables, which will no longer be effective after the pre-depiction of the variable is completed. Now let's take a look at the pre-depreciation of the zone variable. The pre-depoxing program is inserted into the data table in inserting the WHILE ring structure. First create a sample information table, name myTable, and then create a pre-depreciation, named INSERTROWS. In the program, @LOOP_COUNTER and @start_val, we will declare these two variables together, and divide the two variables in a comma. The following is a method for establishing a data sheet and a pre-depreciation using the T-SQL program:
Use mydb
Go
Create Table MyTable
(
Column1 Int,
Column2 char (10)
)
Go
CREATE Procedure InsertRows @start_Value Int
AS
Declare @LOOP_COUNTER INT, @Start_Val Int
Set @start_val = @start_value - 1set @LOOP_COUNTER = 0
While (@LOOP_COUNTER <5)
Begin
INSERT INTO MyTable Values (@start_val 1, "new row")
Print (@start_val)
Set @start_val = @start_val 1
Set @LOOP_COUNTER = @loop_counter 1
End
Go
The pre-depiction of the starting value is now executed, as shown below:
Execute InsertRows 1
Go
After execution, five @start_val values: 0, 1, 2, 3, and 4. Use the following statements to select all the information from MyTable:
SELECT *
From myTable
Go
After executing the SELECT statement, the following output appears:
Column1 column2
-------- ----------
1 new row
2 new row
3 new row
4 new row
5 new row
When the pre-preserved program is completed, @ loop_counter and @start_val will not be available. First use the following T-SQL statement attempt to print:
Print (@LOOP_COUNTER)
Print (@start_val)
Go
Will get this error message:
Server: message 137, level 15, state 2, line 1
The variable '@LOOP_COUNTER' must be declared.
Server: message 137, level 15, state 1, line 2
The variable '@start_val' must be announced.
When performing a batch statement, the rules of this variable range are also applicable. Once the keyword Go (indicating the end of the batch statement), the area variable declared by the batch statement will not be used, and the range of region variables is limited to the batch of use. The following pre-stored programs allow you to understand this rule:
Use northwind
Go
Declare @Price Money
Execute getUnitprice 77, @Unit_price = @price output
Print Convert (VARCHAR (6), @PRICE)
Go
Print Convert (VARCHAR (6), @PRICE)
Go
The first Print statement is printed in a batch operation. The second print statement attempts to print this zone variable after the batch operation is completed, and the following error message will be returned to the following error message:
13.00
Server: message 137, level 15, state 2, line 1
The variable '@price' must be announced.
The first print statement is successful (printing value 13.00).
You may want to use not only one T-SQL statement in the pre-depreciation, such as Begin Transaction, Commit, and Rollback These statements, the method of operation is to first become a unit of transaction, please refer to the details of use Chapter 19.
Using RETURN Use the return key at any time to exit the pre-stored program to return to the call program, or it can be used to exit batch operations. When return is executed, the pre-depressed program is executed to stop execution, and back to the next statement in the call program, Return can also return to the integer value.
First, let's take a look at an example that uses return from the pre-save program. You will modify the previous getUnitprice program to check if the input value is provided. If not, you can print this information to the user and return to the call. To achieve this, please define the input parameter preset value as NULL, then check if the value in the program is NULL, which means that no value is input. The following is the program code:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
WHERE Name = "getUnitprice" and
TYPE = "P")
Drop Procedure GetUnitprice
Go
Create Procedure getUnitprice @prod_id int = null
AS
IF @Prod_id is NULL
Begin
Print "please enter a product id number"
Return
End
Else
Begin
SELECT Unitprice
From product
Where produter = @Prod_id
End
Go
Don't enter the ProductID value now to perform getUnitprice and look at the results. Since the call program statement is not the first statement in the batch operation, the pre-depreciation program must be implemented using the Execute statement. The program code is as follows:
Print "Before Procedure"
Execute getUnitprice
Print "After Procedure Returns from Stored Procedure"
Go
The output results are shown below:
BEFORE Procedure
Please enter a product id number
After Procedure Returns from Stored Procedure
It can be seen from the second Print statement that when the pre-depreciation executes Return, the batch operation will continue to execute at the Print statement.
Now use Return to pass the value into the call program. The promised value must be an integer, constant or variables. Variables must be used in the call program through the declaration. For example, when the input parameter is a product of less than $ 100, the transmitted value 1, otherwise it will return 99.
Create Procedure CheckUnitprice @Prod_ID INT
AS
IF (Select Unitprice
From product
Where produter = @Prod_ID) <100
Return 1
Else
Return 99
Go
In order to call the pre-store and can use the return value, the variation is declared in the call program and the variable is set to the backfinder value of the pre-depreciation, and the input parameter uses the ProductID value 66), the program code is as follows:
Declare @return_val int
Execute @return_val = checkunitprice 66
IF (@return_val = 1) Print 'Unit Price Is Less Than $ 100'GO
Since the specified product is a single price of $ 17, the return value is 1, and the Unit Price is Less Than $ 100 is listed. When the variable is used to save the passage, remember that its data type must be declared as an integer, this is the requirements of returnit statement.
Use the SELECT pass value
You can use SELECT statements in the pre-store to return to the result set from the SELECT query or back to the variable value.
Now let's take a look at several examples. First create a pre-depreciation, named PrintUnitprice, which will return the product unit price specified by the input parameters (through the ProductID), the program code is as follows:
Create Procedure PrintUnitprice @Prod_ID INT
AS
SELECT ProductID,
Unitprice
From product
Where produter = @Prod_id
Go
Pick the 66 as an input parameter value:
PrintUnitPrice 66
Go
The result looks like this:
ProductID Unitprice
---------- ------------
66 17.0000
(Impact 1 data column)
To pass the variable value using the SELECT statement, then enter the variable name after the statement. In the following example, we will rebuild the CheckUnitprice pre-depreciation, return a variable value, and specify the output title:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
Where name = "checkunitprice" and
TYPE = "P")
Drop Procedure CheckUnitprice
Go
Create Procedure CheckUnitprice @Prod_ID INT
AS
Declare @ var1 int
IF (Select Unitprice
From product
Where produter = @Prod_ID)> 100
Set @ var1 = 1
Else
SET @ var1 = 99
SELECT "variable 1" = @ var1
Print "Can Add More Tsql Statements Here"
Go
Pick the 66 as an input parameter value:
CheckUnitPrice 66
Go
The output of executing the pre-depreciation program is as follows:
Variable 1
-------------
99
(Impact 1 data column)
Can Add More T-SQL Statements Here
We print the CAN Add More T-SQL Statements Here to emphasize the difference between the SELECT return value and the use of RETURN transfer values. When the return is called, the pre-depreciation is ending; when the SELECT is called, the pre-depreciation will continue to execute after the SELECT back the result set.
In the previous example, if the output title is not specified, only select @varl is used, and the output will have no title, as shown below:
(Without data line name) ---------------
99
(Impact 1 data column)
Using Enterprise Manager now let us see how to build a pre-depreciated program using Enterprise Manager. To establish a pre-deployment using Enterprise Manager, you must still know how to write T-SQL statements, Enterprise Manager only provides a graphical interface for the establishment. Reconfment on the InsertRows pre-depression now.
In Enterprise Manager expands the MyDB database in the left window, select the Pre-depicting program folder to delete the pre-store, all pre-depreciations are displayed in the right pane, in the InsertRows pre-depreciation program (this chapter has been established, so it should already exist) on the right point The mouse, then select the delete from the fast display function (or renamed or copy the pre-save program from the fast display function table), the sequential dialogue shown in Figure 21-1 will occur, according to all removal Delete the pre-store.
Figure 21-1 "Removing the object" dialogue
Right point in the pre-depreciation program, select the new pre-preserved program from the flash function table, and the window that appears is shown in Figure 21-2.
Figure 21-2 "Prepaid Program Properties" Window
In the text block of the general tab, the name of the pre-depreciation program is used to replace [OWNER]. In this case, it is INSERTROWS, then input T-SQL for the prescaler. Figure 21-3 shows the pre-depicting program properties window after INSERTROWS using the T-SQL program.
Figure 21-3 Adding a new pre-preservation program with a T-SQL program code
Press to check the syntax button, let SQL Server point out the T-SQL syntax error in the pre-program, fix the found syntax error, and reverse according to the syntax check until the syntax check is successful (shown in Figure 21-4), press OK.
Figure 21-4 Showing Syntax Check Success Window
When you press OK to create your pre-save program in the pre-stored program, return to Enterprise Manager, select the pre-stored program folder in the left pane, you can see the newly established pre-stored program in the right pane. As shown in Figure 21-5.
Figure 21-5 Preparation Program in Enterprise Manage
To assign user execution permissions for the new pre-save program, right-click on the right point on the right pane, select Properties in the Snap Function, and the pre-depreciation properties window, press the permission call object properties Windows (shown in Figures 21-6), select whether or not the user or database role is allowed in the Exec Nuclear Correspondence. In this example, we open the use of the INSERTROWS pre-depreciation to three users.
Figure 21-6 "Items" tab of the "object properties" window
Press the set / determined to return to the pre-preserved Properties window, press and click OK.
You can edit the existing pre-stored program: Press the right button on the program name, select the property from the quick function table, edit the program in the pre-depicting Properties window (the conversation block of Figure 21-3), use the check The syntax button checks the syntax, determines the correct, press the set, and then click OK.
You can also use Enterprise Manager to manage existing pre-stored programs permissions, and the right point in the pre-depicting program name in Enterprise Manager, select all work from the flash function table, then select Administrative Use Permissions. In all work, you can also choose to establish a new distribution set to the pre-save (discussed in this book twentyday), generate SQL instruction codes, and display dependence. Assume that the SQL instruction code is selected, SQL Server automatically creates a command code file (by you specifying the instruction code file name), which includes the definition of the pre-stored program. This instruction file can be used when you want to re-establish a pre-store. Use the establishment of the pre-deprecated programs
The third method is to use the establishment of the pre-deposit program wizard, which provides the basic structure of the T-SQL program code required to establish a pre-depression program, which can help you insert, delete or update the information list, but you can't execute Looking for the procedure for information columns.
The elves allow multiple pre-depreciation programs to be created in a database without exiting the wizard and restart. However, if you want to establish a pre-store in another database, you must perform the elves again. When performing the elves, please follow the steps below:
In Enterprise Manager, select Tools / Spirit from the Tool Column to select the wizard dialogue in the Call, select the Establishing Puzzle Spirit in the Database, as shown in Figure 21-7.
Figure 21-7 "Select Elf" dialogue
Select It is determined that the screen that is built to establish a pre-depreciation, as shown in Figure 21-8.
Figure 21-8 "Establishing a Pre-deprecated Program Elf" Welcome Screen
Select Next to display the Library screen, select the database that wants to establish a pre-depreciation in the Database Name. Select the next step to select the prepaid program screen (as shown in Figure 21-9), in this window, you can see all the data table names established in this database, and there are three data sheets containing the verb block, respectively Represents three pre-stored procedures that use the wizard: Insert, delete, and update information, select the appropriate verified block.
Figure 21-9 "Select Preparation Program" screen
This example shows the two data sheets used in this book, where the Bicycle_Inventory data sheet is assigned two pre-depreciation: an insertion program and an updater. As shown in subsequent steps, you can modify it before actually establishing these programs.
Description Of course, a pre-depreciation program can perform multiple types of information modifications, but establishing a pre-depicting program wizard regarding each modified type as a stand-alone pre-depicting program. You can change any of the pre-depreciations established by the Elf with T-SQL program code.
Press Next to display the completion of the establishment of the pre-store, as shown in Figure 21-10, this screen lists the built-in pre-depicting program wizard name and description.
Figure 21-10 "Completing the Establishing Pre-Deprecation Program Elf"
To rename or edit the prepassure, press Edit in the completion of the Build Prepaid Program Wizard, enter Edit Preparation Properties Windows, as shown in Figure 21-11, this screen lists the information line that will affect this preservation program, in selecting The data line names in the data line will be used by the pre-depiction.
Figure 21-11 "Edit Prepaid Program Properties" window
In this example display, in the Bicycle_Inventory table, six data guides are influenced by inserting the pre-depreciation program. This insertion pre-depreciation has been named INSERT_BICYCLE_INVENTORY_1, each data line has a check-in, representing six The value of the data line needs to be entered when the pre-deployment is executed, and six data rows are inserted in the pre-depreciation value to this data sheet. To rename the pre-store, clear the name in the name editing block and replace it with a new name. To edit the pre-store, press Edit SQL to display the editing pre-deposit SQL dialogue, as shown in Figure 21-12, in this dialogue, you can view the T-SQL program of the pre-store program, here the T-SQL is very Basic. In this example, the five parameters listed here will be inserted into the value of the new data. To edit the schedule, simply enter the modification setting in the text block. After completing the editing, press the analysis to check the syntax error, correct the error, and then click OK to return to complete the establishment of the pre-depressed wizard screen. Figure 21-12 "Edit Preparation SQL" dialog
After determining that all the program codes of all pre-deposited programs are correct, press the completion of the setup preservation. After the establishment is complete, don't forget to set permissions for each pre-store program. (Please refer to this chapter to introduce the set permissions
In some respects, the elf is not very useful. So if you know how to write T-SQL, you can use the instruction code or Enterprise Manager to create a pre-store.
Use the T-SQL Management Preparation Now to see how to use the T-SQL command to change, delete and review the contents of the pre-stored program. ALTER Procedure Statement ALTER Procedure T-SQL Statement is used to change the pre-depreciation program established by Create Procedure. Use Alter Procedure to change the pre-depreciation, will not change the permissions set by the original pre-depreciation, nor does it affect any dependent pre-depicting programs (depending on the program, or the trigger program is a program of call pre-depicting programs). ALTER Procedure Statement Syntax and Create Procedure Similar to:
Alter Proc [edure] procedure_name
[{@Parameter_name Data_Type}] [= default] [OUTPUT]
[, ..., n]
ASTSQL_STATEMENT (S)
In the ALTER Procedure statement, you must rewrite the entire preservation program to achieve the required changes. For example, let's rebuild the pre-depreciation of the previous example, and change the pre-store to check the unit price greater than $ 100, as shown below:
Use northwind
Go
IF exissits (SELECT NAME
From sysobjects
WHERE Name = "getUnitprice" and
TYPE = "P")
Drop Procedure GetUnitprice
Go
Create Procedure getUnitprice @prod_id int,
@Unit_price Money Output
AS
SELECT @Unit_price = Unitprice
From product
Where produter = @Prod_id
Go
Alter procedure getUnitprice @prod_id int, @ unit_price mother OUTPUT
AS
SELECT @Unit_price = Unitprice
From product
Where productid = @Prod_ID and
Unitprice> 100
Go
Now use the following statement authorized users Dickb that execute permissions of the pre-depressed program.
Grant Execute on getUnitprice to Dickb
Go
As mentioned earlier, when the pre-depressed program is changed, the authority of the original pre-preservation program will not be changed. Now we change the pre-depreciation, select the data column greater than $ 200 with UNITprice, instead of more than $ 100, the program is as follows:
Alter procedure getUnitprice @prod_id int,
@Unit_price Money Output
AS
SELECT @Unit_price = Unitprice
From product
Where productid = @Prod_ID and
Unitprice> 200
Go
After executing the ALTER Procedure, the user Dickb still retains execution permissions to the pre-save program. Drop Procedure State Drop Procedure T-SQL statement is simple, it is to delete the existing pre-depreciation; the pre-depreciation cannot be recovered after deleting, if you want to re-establish the deleted pre-store, you should use create procedure statement. Reconstruction. Deleting the pre-depreciation will miss all permissions and must be re-authorized after re-establishment. Below is an example of using Drop Procedure to delete the getUnitprice pre-depict:
Use northwind
Go
Drop Procedure GetUnitprice
Go
Description To delete a pre-store, you must use the database belonging to this preservation program. Keep in mind that when you use the database, use the USE statement and enter the database name.
The sp_helptext system pre-depicting program allows you to view the pre-depreciation definition, as well as the pre-depreciation statements (or definitions for the print trigger, checklist, rules, or preset). SP_HELPText is useful when you want to quickly use the ISQL, OSQL, or SQL Query Analyzer call out of the program. You can also assign the output to a file, and you can call the file direct modification when you need to edit or rebuild the pre-depressed program. To use sp_helptext, you must use the name of the user's self-reservation program (or other object name) as a parameter. For example, to view the statement of the InsertRows pre-depreciation program, use the following command:
Use mydb
Go
SP_HELPText InsertRows
Go
The output is as follows:
TEXT
------------------------------------------------
CREATE Procedure InsertRows @start_Value Int
AS
Declare @LOOP_COUNTER INT,
@Start_Val Int
Set @start_val = @Start_Value - 1
Set @LOOP_COUNTER = 0
While (@LOOP_COUNTER <5) Begin
INSERT INTO MyTable Values (@start_val 1, 'new row')
Print (@start_val)
Set @start_val = @start_val 1
Set @LOOP_COUNTER = @loop_counter 1
End
Summary of this chapter
In this chapter, you have learned the use of system pre-deposit procedures and user-owned custom programs, and how to use T-SQL program, Enterprise Manager, and establish a pre-depreciation program of the user's customs; in addition, you Also learn how to use parameters and variables, and how to perform a pre-depreciation, and see T-SQL statements for changes, delete, and review pre-depreciation. Chapter 22 will learn to trigger procedures, trigger programs are a special pre-deployment, which can be automated under specific conditions.
