safish.com

You can use parameters to pass a value into a stored procedure, or to return a value from a stored procedure to a calling program. You can have up to 1024 parameters in a stored procedure.

Parameters can be given default values. The default value can be a constant value or NULL. You do not need to pass a parameter a value if a default is specified.

Example:

  CREATE PROCEDURE MyProcedure
    (
    @Name varchar(200),
    @Age int,
    @Year int = 2000,     /* default of 2000 specified */
    @ParticipantID int OUTPUT
    )
  AS
    SELECT @retval = 0

    INSERT INTO Participants
      (Name, Age, Year, DateEntered)
    VALUES
      (@Name, @Age, @Year, GetDate())

    // Give return variable value of error
    SELECT @retval = @@ERROR    

    // Return the new identity value
    SELECT @ParticipantID = @@IDENTITY
    return