GeekInterview.com
Series: Subject: Topic:

Stored Procedures Interview Questions

Showing Questions 1 - 17 of 17 Questions
Sort by: 
 | 

Difference between view and stored procedure

Asked By: preetiv | Asked On: May 28th, 2008

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.

Star Read Best Answer

Editorial / Best Answer

Answered by: atdhdrolla

View all questions by atdhdrolla   View all answers by atdhdrolla

Member Since Mar-2007 | Answered On : Jul 16th, 2008

View - A View in simple terms is a subset of a table. It can be used to retrieve data from the tables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure -  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.

Answered by: naveenreddy bolla on: Feb 21st, 2013

View: View is virtual table. It is using for security purpose means whenever we need to give only some permissions to the different user then create the view from table with required columns. Their is...

Answered by: biswajit talukder on: Apr 28th, 2012

View is a virtual table. physically is not exit. but procedure is physically exit,which has mainly 2 types
1)local procedure and
2)stored procedure,
store procedure maid by the user. and local procedure made by the SQL.

What is stored procedure?

Asked By: Interview Candidate | Asked On: Aug 19th, 2005

A program running in the database that can take complex actions based on the inputs you send it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL or Java.

Answered by: guruprasad on: Oct 3rd, 2012

Stored Procedure is a re-usable program which is created once and reused n times .
It stores the procedure at database level
It provides security, better performance.

Answered by: narendra9559 on: May 24th, 2011

Stored Procedure is an already written sql statements that is saved in database.

When would you use stored procedure or functions ?

Asked By: jayshree13 | Asked On: Apr 16th, 2011

Explain the scenario of the best usage

Answered by: ptmich on: May 28th, 2012

It is best to go for a function when a value needs to be computed but a stored procedure is useful when you need to execute business logic.

Answered by: narendra9559 on: May 25th, 2011

Hi, suppose you want top 3 salaries from emp table. syntax for top 3 salaries: select distinct top (3) (sal) from emp order by sal desc we have to use this query dialy or monthly.Mea...

What is the difference between stored procedures and stored functions in Oracle?

Asked By: vssandilya | Asked On: Dec 12th, 2005

Answered by: Prasanjeet Das on: May 9th, 2012

SP 1-It is a subprogram that perform an action. 2-It does not contain any return clause. 3-Can return none,one or many values. 4-Can contain a return statement. SF 1-Invoke as a part of expression....

Answered by: krishnaindia2007 on: May 26th, 2008

1. A function is used to compute a value. A porucedure is used to execute business logic.2. A functin must return a value. A procedure may not return a value or may return more than one value using ou...

What is the difference between server and parallel editions ?

Asked By: plvenkatsenthil | Asked On: Oct 12th, 2011

Answered by: sreemon on: Jan 4th, 2012

I feel this Answer is revelent, It will help in understand the concept Difference between Server Jobs and Parallel Jobs Server Jobs works only if the server jobs datastage has been installed in you...

What are external procedures ? Why and when they are used?

Asked By: smocherla | Asked On: Jul 27th, 2006

Answered by: akharejhansi on: Jun 8th, 2011

An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedu...

Answered by: praveengowda on: May 23rd, 2008

External procedures provide a mechanism for calling out to a non-database program, such as a DLL under NT or a shared library under Unix. Every session calling an external procedure will have its own ...

Input parameter

Asked By: papillon | Asked On: Jan 28th, 2010

How do we pass default value as input parameter in a store procedure?

Answered by: nisargkothari on: Sep 16th, 2010

create procedure sp_test
@param1 INT ,
@param2 INT= value

as

select * from testtable1 where value1 = @param1 and value2 = @param2

Answered by: metal on: Jan 31st, 2010

CREATE or REPLACE PROCEDURE proc_test
(name IN varchar2 DEFAULT 'Me' )
AS
BEGIN
dbms_output.put_line('My name is proc_test ' || name);
END;

SQL> exec proc_test('hello');
My name is proc_test hello

SQL> exec proc_test();
My name is proc_test Me

Authid clause

Asked By: deepikazain | Asked On: Mar 9th, 2010

What authid clause does in executing dynamic SQL?

Answered by: dipanjan80 on: Aug 25th, 2010

One can use the AUTHID CURRENT_USER clause to make stored procedures and functions execute with the privileges and schema context of the calling user. You can create one instance of the procedure,...

Call stored procedure dynamically

Asked By: papillon | Asked On: Jan 28th, 2010

How do we call stored procedure dynamically?

Answered by: suneesece on: May 19th, 2010

We can call using dynamic SQL, Execute Immediate.

How do you find which procedures are being used in database?

Asked By: Suman-Delhi | Asked On: Feb 8th, 2007

Answered by: suneesece on: May 19th, 2010

select * from all_objects where object_type= 'PROCEDURE';

Answered by: Shafik Ismail on: Feb 26th, 2010

I am not too sure if I understand our question properly. If you wanted to find all the stored procedures defined to the database with a CREATE PROCEDURE command (let's say DB2), then run a query from ...

Why the stored functions can not be called from dml statements? Why cant we modify the database by function?

Asked By: Appu | Asked On: Jun 10th, 2007

Answered by: ushanaina on: Nov 24th, 2009

Would like to add one more point for your answer,

Calling a function from select stament is not recomended as it will degrade the performance.

Answered by: krishnaindia2007 on: May 26th, 2008

>>Why the stored functions can not be called from DML Statements?  We can call a stored function from dml statement.Here is simple exampleCREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER ISB...

What is the difference between "stored procedure" and "dynamic SQL" ?

Asked By: asd3 | Asked On: Jun 9th, 2007

Answered by: krishnaindia2007 on: May 26th, 2008

Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.

Answered by: sampra on: Mar 10th, 2008

Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.

Can you call procedure in functions

Asked By: chesy | Asked On: May 26th, 2008

Answered by: krishnaindia2007 on: May 26th, 2008

Yes , we can. Here is simple example.

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/


CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER  AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/


SELECT TEST_FUNC() FROM DUAL;

OUTPUT
10

What is the difference between "is" and "as" while creating procedure. Ex:- create procedure is or as?

Asked By: Shekhar | Asked On: Apr 14th, 2006

Answered by: krishnaindia2007 on: May 26th, 2008

Both are same.

Answered by: praveengowda on: May 23rd, 2008

There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS.  Here is the exampleEx: ASPakcage Headercreate package schema.testpkg ...

What is the use of ref cursor, how it is diifferent than the normal cursor.

Asked By: Hemant Tiwari | Asked On: Feb 20th, 2007

Answered by: krishnaindia2007 on: May 26th, 2008

A ref cursor is basically a data type. A variable created based on such data type is called a cursor variable.Dynamic cursors are declared using reference cursors.Differences:1. Cusor is static in nat...

Answered by: Ramesh on: Mar 20th, 2007

Ref Cursor is used to retrieve different Active Set from different Table,
whereas Cursor can retrieve only active set from the same table.

How can I call function in a stored procedure in SQL server?

Asked By: Sridhar Rao | Asked On: Oct 10th, 2006

Answered by: praveengowda on: May 23rd, 2008

A function can be called in a select statement as well as in a stroed proc. As function call would return a value we need to store return value in a variable.Ex:Assume we have a fuction .. FN_TEST(Dat...

Answered by: sampra on: Mar 10th, 2008

pls explain wd simple example  

What is pragma autonomous_transaction ? How to use that ?

Asked By: santanu | Asked On: Aug 8th, 2007

Answered by: senkum123 on: Aug 12th, 2007

PRAGMA AUTONOMOUS_TRANSACTION is used to commit the transaction in the PL/SQL block which is inside another PL/SQL block which is on the verge of rolling back.

To make an independent transaction within a PL/SQL block

Answered by: soorajsk_84 on: Aug 9th, 2007

An autonomous transaction is used to create a seprate transaction which when commited will commit data only belongs to that transactionExample PROCEDURE Proc 1  IS  .  INSERT&...

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.