USE Northwind
   GO

   CREATE PROC spInsertShipper
      @CompanyName   nvarchar(40),
      @Phone         nvarchar(24)
   AS
      INSERT INTO Shippers
      VALUES
         (@CompanyName, @Phone)

  EXEC spInsertShipper 'Speedy Shippers, Inc.', '(503) 555-5566'

Creating Output Parameter's

USE Northwind
   GO

   CREATE PROC spInsertOrder
      @CustomerID       nvarchar(5),
      @EmployeeID       int,
      @OrderDate        datetime      = NULL,
      @RequiredDate     datetime      = NULL,
      @ShippedDate      datetime      = NULL,
      @ShipVia          int,
      @Freight          money,
      @ShipName         nvarchar(40)  = NULL,
      @ShipAddress      nvarchar(60)  = NULL,
      @ShipCity         nvarchar(15)  = NULL,
      @ShipRegion       nvarchar(15)  = NULL,
      @ShipPostalCode   nvarchar(10)  = NULL,
      @ShipCountry      nvarchar(15)  = NULL,
      @OrderID          int      OUTPUT

   AS
      /* Create the new record */
      INSERT INTO Orders
      VALUES
         (
            @CustomerID,
            @EmployeeID,
            @OrderDate,
            @RequiredDate,
            @ShippedDate,
            @ShipVia,
            @Freight,
            @ShipName,
            @ShipAddress,
            @ShipCity,
            @ShipRegion,
            @ShipPostalCode,
            @ShipCountry
         )

   /* Move the identity value from the newly inserted record into
      our output variable */
   SELECT @OrderID = @@IDENTITY

USE Northwind
   GO

   DECLARE @MyIdent int

   EXEC spInsertOrder
        @CustomerID = 'ALFKI',
        @EmployeeID = 5,
        @OrderDate = '5/1/1999',
        @ShipVia = 3,
        @Freight = 5.00,
        @OrderID = @MyIdent OUTPUT

   SELECT @MyIdent AS IdentityValue

   SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName
   FROM Orders
   WHERE OrderID = @MyIdent

Error Handling

USE Northwind
   GO

   DECLARE @Error int

   -- Bogus INSERT - there is no OrderID of 999999 in Northind
   INSERT INTO [Order Details]
      (OrderID, ProductID, UnitPrice, Quantity, Discount)
   VALUES
      (999999,11,10.00,10, 0)

   -- Move our error code into safe keeping. Note that, after this statement,
   --  @@Error will be reset to whatever error number applies to this statement
   SELECT @Error = @@ERROR

   -- Print out a blank separator line
   PRINT ' '

   -- The value of our holding variable is just what we would expect
   PRINT 'The Value of @Error is ' + CONVERT(varchar, @Error)

   -- The value of @@ERROR has been reset - it's back to zero
   PRINT 'The Value of @@ERROR is ' + CONVERT(varchar, @@ERROR)

We can manually raise Errors using raiseerror,

USE Northwind
   GO

   ALTER PROC spInsertDateValidatedOrder
      @CustomerID       nvarchar(5),
      @EmployeeID       int,
      @OrderDate        datetime     = NULL,
      @RequiredDate     datetime     = NULL,
      @ShippedDate      datetime     = NULL,
      @ShipVia          int,
      @Freight          money,
      @ShipName         nvarchar(40) = NULL,
      @ShipAddress      nvarchar(60) = NULL,
      @ShipCity         nvarchar(15) = NULL,
      @ShipRegion       nvarchar(15) = NULL,
      @ShipPostalCode   nvarchar(10) = NULL,
      @ShipCountry      nvarchar(15) = NULL,
      @OrderID          int      OUTPUT

   AS

   -- Declare our variables
   DECLARE   @Error               int
   DECLARE   @BadDate             varchar(12)
   DECLARE   @InsertedOrderDate   smalldatetime

   /* Test to see if supplied date is over seven days old, if so
   ** it is no longer valid. Also test for null values.
   ** If either case is true, then terminate sproc with error
   ** message printed out. */
   IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7 OR @OrderDate IS NULL
   BEGIN
      --RAISERROR doesn't have a date data type, so convert it first
      SELECT @BadDate = CONVERT(varchar, @OrderDate)
      RAISERROR (60000,1,1, @BadDate) WITH SETERROR
      RETURN @@ERROR
   END

   -- We made it this far, so it must be OK to go on with things.
   SELECT @InsertedOrderDate =
      CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
      PRINT 'The Time of Day in Order Date was truncated'
   /* Create the new record */
   INSERT INTO Orders
   VALUES
   (
      @CustomerID,
      @EmployeeID,
      @InsertedOrderDate,
      @RequiredDate,
      @ShippedDate,
      @ShipVia,
      @Freight,
      @ShipName,
      @ShipAddress,
      @ShipCity,
      @ShipRegion,
      @ShipPostalCode,
      @ShipCountry
   )

   -- Move it to our local variable, and check for an error condition
   SELECT @Error = @@ERROR

   IF @Error != 0
   BEGIN
      -- Uh, Oh - something went wrong.

      IF @Error = 547
      -- The problem is a constraint violation. Print out some informational
      -- help to steer the user to the most likely problem.
      BEGIN
         PRINT 'Supplied data violates data integrity rules'
         PRINT 'Check that the supplied customer number exists'
         PRINT 'in the system and try again'
      END
      ELSE
      -- Oops, it's something we haven't anticipated, tell them that we
      -- don't know, print out the error.
      BEGIN
         PRINT 'An unknown error occurred. Contact your System Administrator'
         PRINT 'The error was number ' + CONVERT(varchar, @Error)
      END
      -- Regardless of the error, we're going to send it back to the calling
      -- piece of code so it can be handled at that level if necessary.
      RETURN @Error
   END

   /* Move the identity value from the newly inserted record into
             our output variable */
   SELECT @OrderID = @@IDENTITY

   RETURN

System Defined Stored Procedures,
They all start with sp_

They all reside in the master database

sample system defined sp,

   USE master
   GO

   CREATE PROC sp_showcontig @TableName sysname, @IndexName sysname
   AS
   -- Declare my holding variables
   DECLARE @ID int,
      @IdxID int

   -- Set what I'm looking for
   SET @ID = OBJECT_ID(@TableName)
   --  Get the index id values
   SELECT @IdxID = IndID
   FROM sysindexes
   WHERE id = @ID
      AND name = @IndexName

   -- Get the info I'm really after
   DBCC SHOWCONTIG (@id, @IdxID)
   GO

Happy Coding,