STORED PROCEDURE questions

1. What is the stored procedure (SP)?
Stored procedures are blocks of SQL code which have been compiled and stored on the server. It may be called from client processes or within triggers.
Or
A Stored Procedure is a set of precompiled and preoptimized SQL statements that performs some database operation. Stored Procedures reside where the DataBase resides and you can access them as needed.

2. Why do we need Stored Procedures?
Stored Procedures make our process faster. One time we compile select and then just use it. They give us completely controls how information is stored and retrieved. Security reason. Defined business rule and Maintenance
or
the benefits of using stored Procedures :
n executes much faster, because the path is not created each time of execution of Stored Procedure
n can be shared between multiple users;
n it is much easier to manage them when they stored in DB, then embedded SQL.
or
Use stored procedures to improve:
security
consistency
data integrity
response time

2a. What benefits of Stored Procedures ?
n run faster than the same commands executed interactively as a batch
n reduce network traffic
n enforce consistency throughout the database
n help provide security
n reduce operator error

3. How do you declare a Cursor for the Stored Procedure?
Use Declare cursor statement:
DECLARE emp_pro procedure for GetName
@emp_name = :EditEmpName,:@emp_salary = 5000;

4. How to use Stored Procedure from another one?
Execute by name.

5. What is allowed in Stored Procedure:
n create view
n create default
n create trigger
n create procedure
n create table ? Nothing

6. What is a Remote Procedure?
Remote Procedure is a stored procedure which exists on a different server than the one you are currently connected to. Remote Procedure is when you execute procedures on another SQL server from your local SQL server. Once both servers are properly configured by System Administrator, System administrator does:
1.Both servers must be configured for remote Access
2.Our server must have the remote server in its list of known servers (sp_addserver)
3.The remote server name must be in the interfaces file
4.We Must have added appropriate remote login on the system being accessed
We can execute any procedure on the remote SQL Server using the server name as part of the identifier.
Exec.servername.dbo.procedurename
We can pass value as parameters to a remote procedure from the SQL batch or procedure that contains the EXECUTE statement for the remote procedure. The Result will appear on our local terminal.

7. What are : @@rowcount, @@error?
@@rowcount is a Global variable which stores the number of rows affected by the most recent data modification operation. It is used to check how many rows were affected by a previous @@error a section of code may be executed based on the value returned by error code.

8. What happens if you rename the object referenced by a Stored Procedure?
We must drop and recreate a procedure if we rename any of the objects it references. A stored procedure that references a table or view whose name has been changed may seem to work fine until SQLServer recompiles it. Recompilation takes place for many reasons and without notification to the user.

9. What is sp_depends?
sp_depends is system a procedure that allows us to get a report (list) of the objects referenced by a procedure. We usually use it before when we need to drop or rename object.

10. How do we create a Stored Procedure? Delete it?
Create procedure “name”
@- specify parameters (variable)(if we need any)
as
select . . . .
If we use more than one SQL statement we have to use begin and end keyword
delete storedprocedure - Drop procedure_name

11. Name some of the Sybase system Stored Procedures you made use of. What did you use them for?
The master DB contains useful stored procedures called “system procedures” that access the system tables. System procedures begin with “sp_” and can be run from any DB. The master DB contains a library of useful stored procedures which access the system tables.
Sp_help - prints all database objects and data types
sp_rename renames an object
sp_helpindex indexes information on the table
sp_depends displays dependency information
sp_who displays currently logged on users
sp_helptext prints text of store procedure, trigger, view, default of rule
sp_lock displays information about locks
sp_adduser sp_dropgroup
sp_addgroup sp_droplogin
sp_addlogin sp_dropuser
sp_changegroup sp_lockloginsp
sp_help sp_password
sp_helpdb sp_user_id
sp_helpgroup sp_user_name
sp_helpuser

12. How can you find out who you are?
sp_who
sp_who_am_i

13. Let’s say you have Stored Procedure B that is being called from Stored Procedure A; you performed commit in B and after that procedure A failed. Will changes that have been made in the Procedure B be rolled back?
Yes. In case of a nested Stored Procedure changes are committed only after successful commit in the outermost Stored Procedure. You will not be allowed to put commit statement in nested stored procedure on syntax level.

14. How can you return user-defined error message from a Stored Procedure?
By using SQL function RaiseError( ). The number of errors must be greater than 2000. You should not use transaction statements in triggers. A trigger is a special kind of stored procedure that takes effect when you issue a statement such as INSERT, DELETE, or UPDATE on a specified table or column. Triggers can be used to enforce referential integrity. For example, assume that a certain condition within a trigger is not met and you want to execute a ROLLBACK. Instead of coding the ROLLBACK directly in the trigger, you should use RaisError and test for that particular return code in the DBMS-specific return code (SQLDBCode) property within the referenced transaction object.

15. What utility do you use to create and place Stored Procedure into Sybase DB?
Usually, I use DBA painter. But sometimes I use LAN Workplace and ISQL utility.
You can use ISQL (DOS), WISQL, SQL Object Manager, etc.

16. How can you use a Stored Procedure in PowerBuilder?
We can use them as a Data Source for the DataWindows or we just execute them using EXEC command in PowerBuilder. If we just execute it and the Stored Procedure returns result-set, we have to:
1. Declare variable for a Stored Procedure,for example,
Declare emp_pro Procedure for
pr_GetName @emp_name = :EditEmpName,
@emp_salary = 500
2. Execute (e.g. execute emp_pro)
3. Fetch in a Loop
4. Close Procedure.(close emp_pro) (optional)

17. Why do you use a Stored procedure instead of interactive SQL?
Stored procedures work faster than the same SQL commands executed interactively, because first time a SP is run it is optimized, compiled and stored in cache memory.

18. How do you call stored procedures from the script?
By name: execute sp_name;

19. How do you pass an argument from your script into a stored procedure?
Declare variable with @ sign.
Example
DECLARE Emp_proc procedure for GetName
@emp_name = :Emp_name_var,
@emp_salary = :Emp_sal_var ;
execute Emp_proc;

20. What system Stored procedures do you use working with SP?
SP_helptext - to view the text of store procedure
SP_rename - to rename a store procedure

21. How can be specify parameters for SP?
The parameters can be specified by position or by name.

22. What is a reason to use the default values for parameters in SP?
If we defined default value for parameters in SP we can check if user provided values for parameters when the executed SP.

23. How can we use SP in PB
We can execute SP from the script or use it as a source for DW object.

24. How can we pass value from SP to the user?
We have to define parameters in SP with keyword OUTPUT

25. What statement do you use if you want to execute SP from the script?
DECLARE - to declare SP
EXECUTE - TO EXECUTE SP
FETCH - TO FETCH the result set into the variable (usually in the LOOP)
CLOSE - is necessary if SP returns the RESULT SET

26. If SP is a source for DW object how do you retrieve data?
With PowerScript RETRIEVE() and if SP has parameters we have to provide these parameters in RETRIEVE()

27. How do you call stored procedures from the script?
Declare the PROCEDURE variable. The EXECUTE statement calls the procedure. The FETCH statement returns the result row, copying its values into two variables. SP is an SQL code that resides in the DB rather than in a client application and is executed by the DB server rather than a client workstation. First, call the procedure to generate the result set. Then issue FETCH commands in a loop to retrieve the results one row at a time. The last thing you do—CLOSE the procedure.