UNIT 7

Question 1

______ a set of Transact-SQL (T-SQL) statements that is compiled and stored as a single database objects for later repetitive use.

Answer 1

Stored procedure

Question 2

API

Answer 2

Application Programming Interface

Question 3

logically, a stored procedure consists of

Answer 3

header, body

Question 4

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.

Answer 4

header

Question 5

contains one or more Transact-SQL statements to be executed at runtime.

Answer 5

body

Question 6

the one way to change a stored procedure

Answer 6

drop or re-create

Question 7

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.”

Answer 7

use

Drop Procedure pr_EqipmentByEqTypeID_get

go // this will say that we need to execute the next line of code

Question 8

create stored procedure for MYSQL

Answer 8

CREATE OR REPLACE PROCEDURE pr_getEquipment()

Question 9

other way to write drop and create procedure in MS SERVER

Answer 9

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

Question 10

what is the other way to change a stored procedure?

Answer 10

Alter Procedure statement

Question 11

execute a procedure in mysql

Answer 11

call pr_Get_Name ()

Question 12

execute a procedure in ms server

Answer 12

exec pr_Get_Name()

Question 13

altering procedure in ms server

Answer 13

alter procedure pr_Equipment_Get

Question 14

altering procedure in mysql

Answer 14

create or replace procedure pr_Equipment_Get

Question 15

When you are creating or changing a stored procedure, keep in mind the ____

Answer 15

limits

Question 16

The name of the procedure is a standard ______________. The maximum length of any identifier is ___ characters.

Answer 16

Transact-SQL identifier

128

Question 17

Stored procedures may contain up to ________ input and output parameters.

Answer 17

2,100

Question 18

There are four ways to receive information from a stored procedure:

Answer 18

• Returning result sets
• Using input and output parameters
• Using return values
• Global cursor

Question 19

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.

Answer 19

Transact-SQL statement

Select

Question 20

An alternative way to send values from a stored procedure to the caller is to use a _________ _______

Answer 20

return value

Question 21

It is possible, however, to assign default values________ to the parameters so that the user is not required to supply them.

Answer 21

default values

@make varchar(50) = ‘%’, @model varchar(50) = ‘%’

Question 22

The procedure is designed as a ___________ that accepts T-SQL wild cards. You can execute this stored procedure with normal values:

Answer 22

small search engine

Execute ap_EqIdByMakeModel_List_6 ‘T%’ , ‘Portege%’

Question 23

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.

Answer 23

Passing Parameters by Name

Question 24

Even more important is the opportunity to create a method that makes code more ____ and ____.

Answer 24

readable and maintainable

Question 25

database objects example

Answer 25

• Triggers
• Views
• User-defined functions

Question 26

stored procedure types

Answer 26

1. User-defined
2. System
3. Extended
4. Temporary
5. Global temporary
6. Remote
7. CLR

Question 27

_________ stored procedures are simply plain, stored procedures assembled by administrators or developers for later use.

Answer 27

user-defined

Question 28

use_________ which returns the mnumber of affacted rows per SQL statement;

Answer 28

@@rowcount