There are two ways variables/parameters are used. Normally variables are DECLAREd and the value is supplied as the script runs. The other way is for the value to be supplied BEFORE the script runs. Let's use your example:

Create Procedure JopTransaction

@@Startyear int =null,

@@StartPeriodNo int =null,

...

as

declare @startdate datetime,

declare @enddate datetime

The variables with DECLARE are set during the run time. The other variables (with @@ although they could also have just one @) are set BEFORE the script executes. So, when the script is run, it looks for Startyear and StartPeriodNo values to be provided. If they are not provided, they are set to NULL.

EXEC JobTransaction 2007, 1

That will set @@Startyear to 2007 and @@StartPeriodNo to 1.

EXEC JobTransaction 2007

That will set @@Startyear to 2007, but since there is not a value for @@StartPeriodNo it will be set to NULL.

@Startdate and @enddate will be set to values determined by the script.

When would you use each?

Use the Parameters when you want the user to provide information. For example, the procedure queries a phone book. To find the phone number you need specific information, so you might require the user to input last name and first name. EXEC SearchPhone 'Doe', 'John' or you might require a begin and end date to use for a where clause in a database search.

Internal variables can be used when you have a value that is determined during run time and you need to reuse that value.

//example added after posting//

CREATE PROCEDURE getCompanyAddress

@Emplastname VARCHAR(100),

@Empfirstname VARCHAR(100)

AS

DECLARE @Companyname VARCHAR(100)

SET @Companyname = (SELECT CompanyName FROM TblCompany WHERE EmployeeLName = @Emplastname AND EmployeeFName = @Empfirstname

SELECT CompanyName, Street, City, State, Zipcode

FROM tblCompany

WHERE Companyname = @Company

The user would then run:

EXEC getCompanyInfo 'Doe', 'John'

and get:

Acme 121 Elm Street Chicago IL 99999

-SQLBill