Mastering PowerBuilder

HomePrevious Lesson: Course 3:: Session 29 :: Page 270
Next Lesson:

Stored Procedures
- Frequently Asked Questions

Questions
What is a Stored Procedure?
Who can create a Stored Procedure?
How to create a Stored Procedure?
How to execute a Stored Procedure?
Where the Stored Procedure is stored?
What are the advantages of Stored Procedure?
What are the disadvantages of Stored Procedure?
Can I send parameters to a Stored Procedure?
Are there any restriction on sending parameters to a Stored Procedure?
How the parameters are processed?
Can I get the changed value of a parameter back?
What happens if I send more parameters than expected?
Can I assign a default value to a Stored Procedure, by which I can use it if the user doesn't pass the parameter?
Is the Stored Procedure name unique?
If I and my friend have a Stored Procedure with same name. When I execute the Stored Procedure, which one will get executed?
Can I execute a Stored Procedure that is residing on a remote SQL Server?
Can I execute a COBOL program from a Stored Procedure?
What is an external Stored Procedure?
Explain me about 'sysprocedures' database?
Can I return a value from a Stored Procedure?
What command I should use to print a message on the screen?
What is a temporary table?
How to create a temporary table?
Can I create a temporary table in a Stored Procedure?
What is the life cycle of a temporary table?
If I create a temporary table in a Stored Procedure sp_myproc1, can a procedure sp_myproc2 which is called from sp_myproc1 can access the temporary table?
Explain the 'Set RowCount' behavior in nested Stored Procedure?
What is a 'registered Stored Procedure'?
How do I simulate a cursor in a Stored Procedure?
I am creating a Stored Procedure. In that Stored Procedure, can I refer to a table owned by somebody else; note: I don't have any permission on that table?
How the permission checking mechanism works on a Stored Procedure?
I wrote a Stored Procedure sp1 six months back. I didn't keep the script for that Stored Procedure? Is there any way I can see the source code of that Stored Procedure?
Is there any limitation on how many lines I write on the Stored Procedure?
I have about ten Stored Procedures, all related to salary calculation. Can I group them?
Can a Stored Procedure return multiple result sets?
Can I ask the DataWindow to process all the result sets?
I want to recompile the Stored Procedure? What should I do?
I dropped a table and created it back and inserted a million fresh rows. I want to compile all the Stored Procedure that are referring to this table. Is there any way of doing this?
Can I create a Stored Procedure in the 'tempdb'?
I create a Stored Procedure. Can any one execute that Stored Procedure?
How to allow/disallow others from executing the Stored Procedure?
I created a Stored Procedure. Can I change transfer the Stored Procedure ownership to my friend?
I created a Stored Procedure. By mistake I revoked EXECUTE permission on a Stored Procedure for myself. What will happen?
I wrote a Stored Procedure. In the logic, for certain condition, I want display an error message and stop executing the Stored Procedure. What should I do?
What is a user-defined message?
My DBA has defined certain application specific error messages. Can I use those messages?
How to create a Stored Procedure that refers to a temporary table?
Is it compulsory to use BEGIN and END statements in the Stored Procedure if I am writing more than one line of code in the Stored Procedure?
Can I execute a Stored Procedure from PowerBuilder as if I am calling a function?
Write the SELECT statement to display all user stored Procedures.
List some global variables that you often use.
I am getting t1 already exists error message when I am trying to create the following Stored Procedures. What is wrong in that?
create proc sp1 @Parm1 int
as
if @Parm1 > 0
	select name into #temp1 from mytable1
else
	select alias_name into #temp1 from mytable1
return 0

Answers
What is a Stored Procedure?
A stored procedure is nothing but a set of SQL statements that are stored in the database system tables.
   
Who can create a Stored Procedure?
System Administrator ( called as 'sa' )
Database Administrator (dbo)
Any user in the database who has the permission to create a Stored Procedure.
   
How to create a Stored Procedure?
You can create a Stored Procedure using CREATE PROCEDURE command.
   
How to execute a Stored Procedure?
You can execute a Stored Procedure using EXEC command. However, if the Stored Procedure is the first command in the command set, you can omit EXEC keyword.
   
Where the Stored Procedure is stored?
The source code of the Stored Procedure is stored in 'syscomments' system table.
The compiled version of the Stored Procedure is stored in 'sysprocedures' system table.
   
What are the advantages of Stored Procedure?
Reduces network traffic.
Faster, since they are pre-compiled.
Gives more security control.
Are encapsulated. -> Makes the user to go through the coded logic.
   
What are the disadvantages of Stored Procedure?
There are no disadvantages.
   
Can I send parameters to a Stored Procedure?
Yes, You can send parameters to a Stored Procedure. The parameters can be either by position when you don't refer to the parameter name, OR in any sequence when you refer to variable name. For ex:

EXEC sp1 Val1, Val2, Val3 OR
EXEC sp1 @Parm2=Val2, @Parm1=Val1, @Parm3=Val3

Parameters are separated by commas. When sending string values as parameters, you don't need to enclose the string value in quotations as long as the string value do not include spaces. String values can be enclosed either in single or double quotations. From MS-SQL Server v6.5 onwards, string enclosed in double quotations are parsed by the server. That means, if you want to send any reserved words, use single quotations.

   
Are there any restriction on sending parameters to a Stored Procedure?
Arrays, Text and Image data type parameters are not allowed as parameters. You can send a maximum of 255 parameters to a Stored Procedure.
   
How the parameters are processed?
If you do not include the parameter name in the EXEC statement, then, parameters are referred from left to right by position. For ex:

EXEC sp1 Val1, Val2, Val3 ...

If you include the parameter name, then, parameters are referred by name.

EXEC sp1 @Parm2=Val2, @Parm1=Val1, @Parm3=Val3 ...

The parameters are passed by value. That means, the changed value of the parameter is not available back in the calling procedure.

   
Can I get the changed value of a parameter back?
Yes, you can. You need to declare the parameter with OUTPUT keyword. At execution time also, you need to declare OUTPUT keyword. For ex:

CREATE PROCEDURE sp1 @Parm1 int, @Parm2 int OUTPUT ...

In the above code, @Parm2 is declared with OUTPUT keyword. That means, the changed value of the parameter @Parm2 is available in the calling procedure. Remember, you also need to declare the OUTPUT keyword with EXEC statement also. For ex:

EXEC sp1 @Parm1=@var1, @Parm2=@var2 OUTPUT

If you do not declare the OUTPUT keyword in the EXEC statement, you will not get any error; However, the changed value of the parameter will not be available in the calling procedure.

   
What happens if I send more parameters than expected?
Till version 4.2, supplying excess parameters to a Stored Procedure do not generate an error (at design time or at run-time). In this situation, if you are sending params by position, values are taken from left to right and the excess one are ignored. If you send by name, parameters that do not have declaration in the Stored Procedure are ignored. From v6.0 onwards, sending excess parameters than expected will generate an error.
   
Can I assign a default value to a Stored Procedure, by which I can use it if the user doesn't pass the parameter?
Yes, you can assign a default value to a Stored Procedure parameter. At run-time, if the user supplies a value to that parameter, then the user-supplied value is taken, otherwise, default value is used. While executing a Stored Procedure, You must supply all the parameters that don't have default values. Supplying values to the parameters that have default values is optional.
   
Is the Stored Procedure name unique?
Unique per user in the database. That means, two different users in the same database can have one procedure each with the same name.
   
If I and my friend have a Stored Procedure with same name. When I execute the Stored Procedure, which one will get executed?
If you do not include the owner name while executing the Stored Procedure, SQL Server will check whether you have a procedure with the same name or not. If you have one, it will execute yours. Otherwise, it will check whether the dbo has a procedure with the same name or not. If it is, then, it will execute, otherwise, you will get an error. In summary, SQL Server checks yours first and dbo's. It ignores others unless you specify the owner name. Answer to your question is "yours".
   
Can I execute a Stored Procedure that is residing on a remote SQL Server?
You can execute a remote Stored Procedure (stored on a different Sybase Server) by using the server name. For ex, to execute Stored Procedure sp1 on Srv2 from Srv1, you need to give:

EXEC Srv2.DbName.OwnerName.sp1

To do this, you should be a user in that server and the database OR that server & database should have a guest user. You should also have permission to execute the procedure.

   
Can I execute a COBOL program from a Stored Procedure?
Yes, you can. If you are Microsoft SQL Server user, you can do this either using an external Stored Procedure or ODS (Open Data Services). If you are Sybase SQL Server user, you can do this using OpenServer.
   
What is an external Stored Procedure?
This concept came from Microsoft. Using an external stored procedure, you can run any non-SQL Server commands -- such as sending a e-mail, listing all the files in a directory, etc.
   
Explain me about 'sysprocedures' database?
This is one of the system database from SQL Server version 10.0 onwards. Installation of this database is optional.
   
Can I return a value from a Stored Procedure?
A Stored Procedure can return a return value using RETURN statement. Return values till 99 are reserved for system usage. However, SQL Server don't stop you from returning a value that is in the system range. In case of system error, your return value supersedes the system value; That means, you will never be able to receive system messages, if the system is trying to send any.
   
What command I should use to print a message on the screen?
You can use either SELECT or PRINT statement. If you want to display an error, you can use raiserror statement.
   
What is a temporary table?
Temporary tables are tables that created in the 'tempdb' database. Only first thirteen characters of a temporary table are unique. Sybase adds some unique number to your temporary table. Even though Sybase adds some numbers to the temporary table, you (as the developer) can refer to the temporary table with the same name you used to create it. If somebody want to refer to it, then they have to use all the name by looking in the temporary database.
   
How to create a temporary table?
Temporary tables are created either using CREATE TABLE statement or SELECT INTO statement.
   
Can I create a temporary table in a Stored Procedure?
Yes, you can.
   
What is the life cycle of a temporary table?
Temporary tables created in a Stored Procedure no longer available as soon as the Stored Procedure execution is completed. Temporary tables create with explicit prefix 'tempdb' are available till the SQL Server is rebooted. If you create a temporary table interactively, without explicit prefix 'tempdb', then, that table is available till you logout. If you prefix 'tempdb', then, that table is available till the SQL Server is rebooted.
   
If I create a temporary table in a Stored Procedure sp_myproc1, can a procedure sp_myproc2 which is called from sp_myproc1 can access the temporary table?
Temporary tables created in the calling Stored Procedure are available in the called Stored Procedure. The vice versa is not available.
   
Explain the Set RowCount behavior in nested Stored Procedure?
RowCount that is set in the calling procedure affects nested procedures. That means, if you set the row count to 10 and call a procedure that selects all the rows from a million row table, you will see only 10 rows, not million rows.
   
What is a 'registered Stored Procedure'?
Registered procedures are used in the OpenServer and you don't have to worry about these unless you are a OpenServer programmer.
   
How do I simulate a cursor in a Stored Procedure?
You can declare a variable similar to the key value of the table and simulate the cursor using Set RowCount and in a loop. For ex:
create proc sp1
as
declare @v1 int
/* declare other variables */
select @v1 = 0
set rowcount 1
while true
   select @v1=col1, @v2=col2, @v3=col3...
      from table1
      where col1 > @v1
   if @@rowcount = 0
      break
   /* Do your processing */
loop
set rowcount 0
return 0
   
I am creating a Stored Procedure. In that Stored Procedure, can I refer to a table owned by somebody else; note: I don't have any permission on that table?
In the Stored Procedure, you can refer to other's table even though you don't have any permission on that table. However, at run-time the Stored Procedure will fail because of privilege problem.
   
How the permission checking mechanism works on a Stored Procedure?
The person who creates the procedure will become the owner of that Stored Procedure. Even the dbo or sa can't execute that Stored Procedure unless the owner of the Stored Procedure grants execute permission on the Stored Procedure to them. However, sa or dbo can execute other's Stored Procedure even if they don't have execute permission on that, by becoming the owner of that Stored Procedure using SET USER command. If the Stored Procedure and the table owner is same, then Sybase don't check the permissions on the table and the Stored Procedure will be successfully executed. If the owner of the Stored Procedure is different from the table, then Sybase checks the permissions on the table.
   
I wrote a Stored Procedure sp1 six months back. I didn't keep the script for that Stored Procedure? Is there any way I can see the source code of that Stored Procedure?
To see the text of the Stored Procedure, you need to execute sp_helptext Stored Procedure with the procedure name as the parameter. For ex:

EXEC sp_helptext sp1

   
Is there any limitation on how many lines I write on the Stored Procedure?
The source can be the maximum number that can be stored in a smallint data type multiply by 255 characters, if you are writing in English. However, there is 64K limitation on the size of the executable plan in versions till version 4.2 (MS-SQL Server)/10.0 (Sybase), . However, in the higher versions, the limitation is removed.
   
I have about ten Stored Procedures, all related to salary calculation. Can I group them?
You can create a group of Stored Procedure with the same name with the group number as suffix separated by semicolon. For ex:

CREATE PROCEDURE sp1 ...
CREATE PROCEDURE sp1;2 ...
CREATE PROCEDURE sp1;3 ...

You can't drop a member of a Stored Procedure group. You have to drop whole group and recreate them.

   
Can a Stored Procedure return multiple result sets?
Yes, in a Stored Procedure, you can return can return as many result sets as you wish.
   
Can I ask the DataWindow to process all the result sets?
No, a DataWindow can deal with only one result set at a time. OUTPUT parameters and return value can't be accessed in a DataWindow. You can read multiple result sets of a Stored Procedure in embedded SQL by fetching one more time after you get SQLCODE 100. Similarly you can access OUTPUT values also (You need to declare it as OUTPUT in the declaration). You can't access the return value
   
I want to recompile the Stored Procedure? What should I do?
Execute the Stored Procedure with WITH RECOMPILE option. for ex:

EXEC sp1 WITH RECOMPILE

   
I dropped a table and created it back and inserted a million fresh rows. I want to compile all the Stored Procedure that are referring to this table. Is there any way of doing this?
Call the sp_recompile Stored Procedure with the table name as the parameter. This procedure make a note of your instruction. Whenever you execute a table for the first time after sp_recompile execution, SQL Server checks whether that table is being referenced in this procedure and compiles accordingly.
   
Can I create a Stored Procedure in the 'tempdb'?
Yes, but, remember that, anything that you create in 'tempdb' is gone when the SQL Server is rebooted.
   
I created a Stored Procedure. Can any one execute that Stored Procedure?
Nobody can execute your Stored Procedure unless you grant permission on that procedure. Even the dbo or sa can't execute that Stored Procedure unless the owner of the Stored Procedure grants execute permission on the Stored Procedure to them. However, sa or dbo can execute other's Stored Procedure even if they don't have execute permission on that, by becoming the owner of that Stored Procedure using SET USER command.
   
How to allow/disallow others from executing the Stored Procedure?
GRANT EXEC permission to users/groups whom you want to allow, using GRANT command. REVOKE EXEC permission to users/groups whom you want to allow, using REVOKE command.
   
I created a Stored Procedure. Can I change transfer the Stored Procedure ownership to my friend?
No, no body, including owner of the Stored Procedure, dbo, sa can transfer the ownership of an object such as table, view, Stored Procedure to other. Only sa can change the ownership of the database.
   
I created a Stored Procedure. By mistake I revoked EXECUTE permission on a Stored Procedure for myself. What will happen?
You don't have to worry about it.
   
I wrote a Stored Procedure. In the logic, for certain condition, I want display an error message and stop executing the Stored Procedure. What should I do?
User RaiseError command and RETURN statement.
   
What is a user-defined message?
User-defined message allows you defining application specific messages and use them in the application without hard-coding the messages in the front-end or in stored procedures. This makes the application more maintainable. All the user-defined messages are stored in 'sysusermessages' table. You can also define parameters in this message by placing place holders using percent sign.
   
My DBA has defined certain application specific error messages. Can I use those messages?
Yah! Sure.
   
How to create a Stored Procedure that refers to a temporary table?
If you are creating a Stored Procedure that refers to the temporary table created by another Stored Procedure, create that temporary table interactively and then create the Stored Procedure. Drop the created temporary table.
   
Is it compulsary to use BEGIN and END statements in the Stored Procedure if I am writing more than one line of code in the Stored Procedure?
Inclusion of BEGIN and END statements in the Stored Procedure body are optional.
   
Can I execute a Stored Procedure from PowerBuilder as if I am calling a function?
As long as a Stored Procedure don't return a result set, you can execute a Stored Procedure as a function from PB, by declaring the Stored Procedure as an external function in a transaction/SQLCA user object. In this case, you can access the return value of the Stored Procedure.
   
Write the SELECT statement to display all user stored Procedures.
SELECT name FROM sysobjects WHERE type = "P"
   
List some global variables that you often use.
@@Error - contains the status of the last executed statement.
@@RowCount - contains the number of rows affected by the last SQL statement.
@@NestLevel - contains the nested call number.
   
I am getting 't1 already exists' error message when I am trying to create the following Stored Procedures. What is wrong in that?
create proc sp1 @Parm1 int
as
	if @Parm1 > 0
		select name into #temp1 from mytable1
	else
		select alias_name into #temp1 from mytable1
return 0
In a Stored Procedure, you can't use multiple SELECT INTO statements targeting the same table.
HomePrevious Lesson: Course 3:: Session 29 :: Page 270
Next Lesson: