Types of Stored Procedures & Functions


It may sound tricky question if someone ask you “What are the types of Stored procedures?”. Because you might have heard of “Type of Functions”. But some of newbie’s or sometimes some of our DB professionals got confused when they hear this question  (just like me – in my earlier days). In this short article, I’ll summarize the Types of Stored procedure. I hope it helps everyone.

Types of Stored procedure

  • System Stored Procedures – System Stored procedures are nothing but in-built stored procedures which deals which the internal metadata of the SQL Server. There is bunch of system stored procedure, for more details refer to MSDN or link : http://msdn.microsoft.com/en-us/library/ms187961.aspx

EX. sp_renamedb. This system procedure helps to rename the database name.

  • User-defined Store Procedures  – User Defined Store procedures are deals with the relational data in the database, created by the end-user.
    • Simple Stored procedure – In this type of SP, there are no In / Out parameter involve. Stored procedure with simple SQL Query with / without JOINS.

      CREATE PROCEDURE dbo.usp_GetPersons()
      BEGIN
          SET NOCOUNT ON

          SELECT * FROM dbo.Persons
      END

  • Parameterized Stored procedure – In this type of SP, there are In / Out / Both parameter involve.

    CREATE PROCEDURE dbo.usp_GetPersons( @PersonKey )
    BEGIN
        SET NOCOUNT ON

        SELECT * FROM dbo.Persons WHERE PersonKey = @PersoneID
    END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s