Benefits of Stored Procedure and User Defined Functions

Benefits of Stored Procedures

• Precompiled execution: SQL Server compiles each Stored Procedure once and then re utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
• Reduced client/server traffic: If network bandwidth is a concern in your environment then you’ll be happy to learn that Stored Procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction: Stored Procedures can be used by multiple users and client programs. If you utilize them in a planned manner then you’ll find the development cycle requires less time.
• Enhanced security controls: You can grant users permission to execute a Stored Procedure independently of underlying table permissions.

Benefits of User Defined Functions

• They allow modular programming: You can create the function once, store it in the database, and call it any number of times in your program. User Defined Functions can be modified independently of the program source code.
• They allow faster execution: Similar to Stored Procedures, Transact-SQL User Defined Functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.
• They can reduce network traffic: An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client

Difference between stored procedure and user defined functions.

Sr.No.	User Defined Function	Stored Procedure
1	Function must return a value.	Stored Procedure may or not return values.
2	Will allow only Select statements, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete and so on
3	It will allow only input parameters, doesn’t support output parameters.	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling we can use try catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored Procedures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as temporary table in it.
7	Stored Procedures can’t be called from a function.	Stored Procedures can call functions.
8	Functions can be called from a select statement.	Procedures can’t be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9	A UDF can be used in join clause as a result set.	Procedures can’t be used in Join clause

Four Levels of Data validation on PowerBuilder Datawindows

Powerbuilder validates a data item before it can be moved from the edit control to the underlying dataitem. Validation begins automatically when the data is changed and the user presses the enter key or tabs to another field in the datawindow control thus changes the focus. Validation can also be forced using on AcceptText() function.

During the time the data is being validated the contents of the editcontrol and values of the item may be different. The data is moved from the editcontrol to the underlying item only when it passes all four levels of validation. These four levels of validation are:

• Did the user change any data?
• Whether the user-entered data (in edit control) and the underlying data item are of the same data type
• Validation rules specified for columns through the datawindow object painter
• Advanced validation done through the itemchanged event

I Level - Did the user change any data?
In this level, the datawindow control tests the contents of the edit control to see if it is different from the data in the item in the datawindow control. When the user simply tabs to a different field, the datawindow control knows that nothing has changed, stops further validation, and allows the user to move to a different field. If the values has changed, the validation continues on to the next level.

II Level - Data type check
PB checks the datatype of the edit control matches the datatype of the underlying item in the datawindow control. If they are the same validation continues to the next level. If the validation fails the user receives a standard message informing that the entry did not pass the validation rules and the cursor is returned to the edit control. You can also write a custom error message. Gettext() functions retrieves the contents of the edit control. Getltemxxx() retrieves the contents of the item in the edit control. You must use one of the following Getitem function that corresponds to the underlying data type.
GetltemString()
GetltemNumber()
GetltemDate()
GetltemTime()
GetltemDateTime()

But remember the data type of editcontrol is always string and the GetText function always returns a string.

III Level - Validation rules
You can define validation rules for a column when you create a datawindow object.

IV - Level - ItemChanged Event
The itemchanged event is the fourth and final level of validation. You can use this for integrity checking and cross table validations

How to Open CHM fils/PB Help files in Windows Vista, 7, 8, 8.1, Server 2008

Many may came across this situation where the PowerBuilder Help file is not opening on Windows Vista, 7, 8, 8.1, Server 2008. For those people go to the following link and download the update package according to your operating system. If this link is not available on the send me a mail, I will send you the update packages for the following OS.
    
   
        
Link:
 
https://support.microsoft.com/en-us/help/917607/error-opening-help-in-windows-based-programs-feature-not-included-or-help-not-supported

Get then other row values in Datawindow Expression

We can access column values of other rows than the current one, by using the [] array notation. 

E.g., a computed column giving the sum of a column in the current row with another column contained in the previous row, could have the expression:

if( getrow()>1, Value1+Value2[-1], Value1 )