Sample Stored Procedure example

Sample Stored Procedure example

Stored procedure is a bunch of SQL statements which forms a logical unit and execute for a particular task and it’s used to encapsulate set of operations or queries to execute on database server. Once stored procedure created it gets compiled and executed with different parameters with results at run time. It can have any combination of output, input, output and input/output parameters. There are many different name used for Stored procedure example: SP, StoreProc, proc, StoredProc, sproc, proc etc…

Stored procedures also return result sets which is results of select statement on the tables. You can also defined variables for processing data and cursors which allows to loop through multiple rows on the table. Stored procedure flow control statements generally includes CASE statements, LOOP, IF, WHILE, REPEAT statements and more.

To understand better let’s create on sample table:

  • Create table scripts:

  • Insert data script:

  • Now we have created table Test with some dummy data. To get data from the table we use below select statement:

  • This will give below results as I have inserted 4 rows of data:

Sample Stored Procedure example

  • Now to convert select statement to stored procedure is fairly simple as below:

  • If you execute newly created stored procedure it will also give same results as select statement:

Sample Stored Procedure example

If you want to include input parameter which will be included in select statement where clause as below:

  • Select statement with where clause:

  • Stored procedure:

  • Execute above stored procedure will give same result as select statement:

Sample Stored Procedure example


Leave a Reply

Your email address will not be published. Required fields are marked *