-
Input Parameter
How do we pass default value as input parameter in a store procedure?
Priyanka Roy
- Dec 6th, 2017
DECLARE @Parameter1 varchar(max)=PRIYANKA
Code- DECLARE
- @Parameter1 varchar(max)=PRIYANKA
nisargkothari
- Sep 16th, 2010
Create procedure sp_test@param1 INT ,@param2 INT= valueas select * from testtable1 where value1 = @param1 and value2 = @param2
-
-
What is the difference between stored procedures and stored functions in ORACLE?
qptopm
- Jul 22nd, 2016
Functions have a scalar return value. Procedures do not have a return value.
A Stored procedure may have arguments that are IN, OUT, or INOUT. Functions may only have IN arguments.Prasanjeet Das
- 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....
-
What is stored procedure?
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.
-
When would you use stored procedure or functions ?
Explain the scenario of the best usage
krishna
- Dec 17th, 2015
When there is return value then we use function else we use procedure and for DMLs also
ptmich
- 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.
-
Difference between View and Stored Procedure
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.
Read Best Answer
Editorial / Best Answer
atdhdrolla- Member Since Mar-2007 | 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.
Leena Roja
- Jul 9th, 2015
Views are virtual tables or subset of permanent tables for easier access of data which is stored in multiple tables. Views contains rows, columns just like a real table. The fields in a view are noth...
Test
- Oct 16th, 2013
View does not have parameters , SP can have input / output parameters . View is another way to view table data . SP is pre-compiled object which can change /add data into tables.
-
How can I call function in a stored procedure in SQL Server?
Manas
- Jun 5th, 2015
Codepraveengowda
- 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...
-
SQL-Transaction in Nested stored procedure
Can we use SQL-Transaction in Nested stored procedure(Stored procedure calling from stored procedure)?
One SQL-Transaction object in primary SP.
second SQL-Transaction object in nested Sp(Second Sp). -
Explain the usage of type record, with datatype of the records as associative array
ex:
type ty_1 is table of number index by plsql_integer;
type ty_2 is table of varchar2(10) index by plsql_integer;
type ty_3 is table of date index by plsql_integer;
TYPE rec_tab IS RECORD ( l_num ty_1, l_str ty_2, l_date ty_3);
Please explain this with an example having loops -
-
What are external procedures ? Why and when they are used?
akharejhansi
- 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...
praveengowda
- 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 ...
-
Authid Clause
What authid clause does in executing dynamic SQL?
dipanjan80
- Aug 26th, 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
How do we call stored procedure dynamically?
-
How do you find which procedures are being used in database?
Shafik Ismail
- Feb 27th, 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?
ushanaina
- 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.
krishnaindia2007
- 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" ?
krishnaindia2007
- May 27th, 2008
Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.
sampra
- Mar 10th, 2008
Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.
-
IS or AS?">
What is the difference between "IS" and "AS" while creating procedure. Ex:- create procedure IS or AS?
praveengowda
- 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.
krishnaindia2007
- 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...
Ramesh
- 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.
-
What is PRAGMA AUTONOMOUS_TRANSACTION ? How to use that ?
senkum123
- 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
soorajsk_84
- 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&...
Read Best Answer
Ans