SQL Server stored procedure - August 29, 2008 at 18:00
PM by Nishant Kumar
Define Stored Procedures.
A stored procedure is a compiled set of Transact-SQL statements.
The business logic can be encapsulated using stored procedure.
It improves network traffic by running set of Transact-SQL statements at one
go.
Define extended Stored Procedures.
An extended stored procedure compiles as DLL and are created to expand
capabilties of user defined stored procedure. It uses xp_ prefix as naming
convention.
SQL Server Stored procedure - Nov 20, 2008 at
18:00 PM by Rajmeet Ghai
Define stored procedure.
Answer
Stored procedure is a set of SQL commands that have been complied and
stored on the database sever. They can be used in the code as and when required
since hey stored. They need not be complied over and over again. They can be
invoked by CALL procedure (..) or EXECUTE procedure(..)
What are the purposes and advantages stored procedure?
Answer
Purposes and advantages of stored procedures:
-
Manage, control and validate data
-
It can also be used for access mechanisms
-
Large queries can be avoided
-
Reduces network traffic since they need not be recompiled
-
Even though the stored procedure itself may be a complex piece of code, we need
not write it over and over again. Hence stored procedures increases reusability
of code
-
Permissions can be granted for stored procedures. Hence, increases security.
Determine when to use stored procedure to complete SQL Server
tasks.
Answer
-
If a large piece of code needs to be performed repeatedly, stored procedures
are ideal
-
When hundreds of lines of SQL code need to be sent; it is better to use stored
procedure through a single statement that executes the code in a procedure,
rather than by sending hundreds of lines of code over the network.
-
When security is required.
Also read
Answer - The Bulk Copy is a command utility that transfer SQL
data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other
database....
Answer - Distributed Query is a query which can retrieve data
from multiple data sources including distributed data........
Answer - Temporary Stored Procedure is stored in TempDB
database. It is volatile and is deleted once connection gets terminated or
server is restarted......
Answer - The column or columns of the table whose value
uniquely identifies each row in the table is called primary key. You can define
column as primary key using primary key constraint while you create table.....
Answer -
Index can be thought as index of the book that is used for fast retrieval of
information. Index uses one or more column index keys and pointers to the
record to locate record.........
The answers to following questions will be made available soon. Keep
visiting.
Define Stored procedure.
What are the purposes and advantages stored procedure?
Determine when to use stored procedure to complete SQL Server tasks.
Explain the categories of stored procedure i.e. System stored procedure, local
stored procedure, temporary stored procedure, extended stored procedure, remote
stored procedure.
Explain nested stored procedure. Syntax and an example for create nested stored
procedure.
What security features are available for stored procedure?
In what three ways is the RETURN statement used in a stored procedure.
|
