I kwew how to make stored procedure and call from Vb application. But question is, What is the right time to used a Stored Procedure in the application. Is anybody out thier that can give a sample scenario when to used a stored procedure in my application.
It depends how often the application calls the databases to perform the transactions/updations. If there are frequent updations to the table(s) using same process then assigning the SELECt/UPDATE statements in a stored procedure and control them in transaction mode to maintain the data consistency.
It is considered a "best practice" in database application development to NEVER let the user interface directly query the database tables. All exchange of data should be done through either stored procedures or views.
If it's not practically useful, then it's practically useless.