UNIT 7 Flashcards
______ a set of Transact-SQL (T-SQL) statements that is compiled and stored as a single database objects for later repetitive use.
Stored procedure
API
Application Programming Interface
logically, a stored procedure consists of
header, body
defines the name of the stored procedure, the input and output parameters, and some miscellaneous processing options. You can think of it as an API declaration of the stored procedure.
header
contains one or more Transact-SQL statements to be executed at runtime.
body
the one way to change a stored procedure
drop or re-create
Two ways to prevent the error “Msg 2714, Level 16, State 3, Procedure pr_Eqipment_Get, Line 4
There is already an object named pr_Equipment_Get’ in the database.”
use
Drop Procedure pr_EqipmentByEqTypeID_get
go // this will say that we need to execute the next line of code
create stored procedure for MYSQL
CREATE OR REPLACE PROCEDURE pr_getEquipment()
other way to write drop and create procedure in MS SERVER
IF object_id(‘ pr_equipment_get’) is not null – we check if theres an object called pr_equipment_get,if it exists,delete it
begin
drop PROCEDURE pr_equipment_get
end
what is the other way to change a stored procedure?
Alter Procedure statement
execute a procedure in mysql
call pr_Get_Name ()
execute a procedure in ms server
exec pr_Get_Name()
altering procedure in ms server
alter procedure pr_Equipment_Get
altering procedure in mysql
create or replace procedure pr_Equipment_Get
When you are creating or changing a stored procedure, keep in mind the ____
limits
The name of the procedure is a standard ______________. The maximum length of any identifier is ___ characters.
Transact-SQL identifier
128
Stored procedures may contain up to ________ input and output parameters.
2,100
There are four ways to receive information from a stored procedure:
- Returning result sets
- Using input and output parameters
- Using return values
- Global cursor
To obtain a result set from a stored procedure, insert a ____________ that returns a result set into the body of the stored procedure. The simplest way is by using a ____ statement, but you could also call another stored procedure.
Transact-SQL statement
Select
An alternative way to send values from a stored procedure to the caller is to use a _________ _______
return value
It is possible, however, to assign default values________ to the parameters so that the user is not required to supply them.
default values
@make varchar(50) = ‘%’, @model varchar(50) = ‘%’
The procedure is designed as a ___________ that accepts T-SQL wild cards. You can execute this stored procedure with normal values:
small search engine
Execute ap_EqIdByMakeModel_List_6 ‘T%’ , ‘Portege%’
You do not have to follow parameter order if you pass parameters by name. You must type the name of the parameter and then assign a value to it. The parameter name must match its definition, including the @ sign.
Passing Parameters by Name
Even more important is the opportunity to create a method that makes code more ____ and ____.
readable and maintainable
database objects example
- Triggers
- Views
- User-defined functions
stored procedure types
- User-defined
- System
- Extended
- Temporary
- Global temporary
- Remote
- CLR
_________ stored procedures are simply plain, stored procedures assembled by administrators or developers for later use.
user-defined
use_________ which returns the mnumber of affacted rows per SQL statement;
@@rowcount
