CoolInterview.com - World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.




Get it now !!
Send your Resume to 6000 Companies
Stored Procedures Interview Questions & Answers - Learning Mode
COOLINTERVIEW.COM DATABASE INTERVIEW QUESTIONS STORED PROCEDURES INTERVIEW QUESTIONS QUESTIONS & ANSWERS - LEARNING MODE

Stored Procedures Interview Questions & Answers - Learning Mode

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database data dictionary.

Try Stored Procedures Interview Questions & Answers - Exam Mode

1 2 Next

Sort By : Latest First | Oldest First | By Rating

Stored Procedures Interview Questions & Answers - Learning Mode
Try Stored Procedures Interview Questions & Answers - Exam Mode
Question: Can we use commit inside the trigger? If not then how can we save the transxaction made by the trigger?

Answer: Using pragma concept. Source: CoolInterview.com
Question: What is the difference between "IS" and "AS" while creating procedure. Ex:- create procedure IS or AS



Answer: Both are equivalent .. Just replacement of DECLARE keyword in declarative section. Source: CoolInterview.com
Question: what is the difference between stored procedures and stored functions in ORACLE



Answer: Both(functions and Procedures) are the Oracle Objects that work explicitly towards database objects like Tables,Views.

The diff. b/t Stored Procedures and Functions

1) The procedures doesn't return values. whereas the function returns value.

2) The procedures accept more than one argument whereas the functions not.
Source: CoolInterview.com
Question: How can we call stored procedures inside store procedures?

Answer: Alter procedure CallAnotherProcedure
as
exec sp_helptext CallAnotherProcedure Source: CoolInterview.com
Question: What is stored procedure?



Answer: A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and inpu Source: CoolInterview.com
Question: Explain about the process which takes place to execute a Stored routine?

Answer: CREATE PROCEDURE and CREATE FUNCTION statement are used to create stored routine. It can act as a function or a procedure. A procedure can be called by using a call statement and pass output with the help of output variables. It can call other Stored routines and it can be called from the inside of a statement. Source: CoolInterview.com
Question: Where the procedures are stored in database?

Answer: A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary. Source: CoolInterview.com
Question: Explain about the difficulties faced by the database developer in implementing pre compiled statements?

Answer: There are many difficulties for implementing pre compiled statements because it should have all the arguments provided to it during compile time. It also depends upon the database and configuration. Performance also varies and it largely depends upon whether it is a generic query or user defined functions. Source: CoolInterview.com
Question: how many types of stored procedure?

Answer: Stored Procedure are of two type
1-user define stored procedure
2- System define Stored procedure
Source: CoolInterview.com
Question: What is the difference between stored procedures and stored functions in ORACLE



Answer: Both(functions and Procedures) are the Oracle Objects that work explicitly towards database objects like Tables,Views.

The diff. b/t Stored Procedures and Functions

1) The procedures doesn't return values. whereas the function returns value.

2) The procedures accept more than one argument whereas the functions not.
Source: CoolInterview.com
Question: Explain the benefits of running stored procedure on a database engine?

Answer: Stored procedures can run directly run on a data base engine. In industries where automation is the key a stored procedure can run entirely on the data base provided to it and this runs on a specialized data base server. Network communication can be avoided. Also this procedure is useful for execution of complex SQL statements. Source: CoolInterview.com
Question: How one call DDL statement using stored procedures in Oracle?

Answer: Yes, we can call DDL command using EXECUTE IMMEDIATE command Source: CoolInterview.com
Question: What are external procedures ? Why and when they are used?



Answer: External procedures are Extended stored procedures only. They let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run.Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API. Source: CoolInterview.com
Question: State the different extensions for stored procedures?

Answer: Most of the database systems have proprietary and vendor based extensions. Microsoft allows procedures to be written using Transact-SQL. Oracle calls its extension as PL/SQL. DB2 has its extension as PL/SQL. PL/pgSQL is the extension used by Postgre SQL and this allows users to have their own functional language such as pl/PHP and pl/Perl. Source: CoolInterview.com
Question: Does storing of data in stored procedures increase the access time? Explain?

Answer: Data stored in stored procedures can be retrieved much faster than the data stored in SQL database. Data can be precompiled and stored in Stored procedures. This reduces the time gap between query and compiling as the data has been precompiled and stored in the procedure. To avoid repetitive nature of the data base statement caches are used. Source: CoolInterview.com
Question: What is cursors?

Answer: Cursors are supported by procedures, functions and triggers. Syntax of the cursors is embedded in SQL. Cursor should be declared before declaring handles. Before declaring cursors it is imperative to declare variables and conditions. Source: CoolInterview.com
Question: What are the uses of stored procedure?

Answer: Stored procedures are often used for data validation and as access control mechanism. Logic applied in applications can be centralized and stored in applications. Complex procedures and functionalities which require huge amount of data processing and logic implementation access their data by procedures. Data is stored in these procedures and accessed by procedures. Source: CoolInterview.com
Question: Explain about recursive stored procedures?

Answer: Recursive stored procedures are used for performing repetitive tasks. Recursive feature is disabled by default but can be activated by using the following command on the server max_sp_recursion_depth, also don?t forget to rename the system variable to a non zero variable. Source: CoolInterview.com
Question: what is the Difference between View and Stored Procedure?

Answer: What is the difference between View and Stored Procedure? Can views accept input parameters as stored procedure? If yes,how could I do that? I am new to database and working on MS SQL,if someone could suggest me good reference websites then that would be great. Source: CoolInterview.com
Question: Explain about the implementation of stored procedures?

Answer: Implementation of stored procedure varies for different databases and vendors. Stored procedures are very flexible and they can be implemented in variety of languages. Stored procedures written in non SQL languages may have a very little compatibility with SQL. Source: CoolInterview.com

1 2 Next



India News Network
Latest 20 Questions
Payment of time- barred debt is: (a) Valid (b) Void (c) Illegal (d) Voidable
Consideration is defined in the Indian Contract Act,1872 in: (a) Section 2(f) (b) Section 2(e) (c) Section 2(g) (d) Section 2(d)
Which of the following is not an exception to the rule, "No consideration, No contract": (a) Natural love and affection (b) Compensation for involuntary services (c) Completed gift (d) Agency
Consideration must move at the desire of: (a) The promisor (b) The promisee (c) The promisor or any other party (d) Both the promisor and the promisee
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
Cache = 0 Seconds