Passing parameter

• thaya2002

  Old Hand

  Points: 363

  More actions

  February 5, 2008 at 3:58 pm

  #186345

  hi all,

  I am new to Stored procedures

  Create Procedure JopTransaction

  @@Startyear int =null,

  @@StartPeriodNo int =null,

  ...

  as

  declare @startdate datetime,

  declare @enddate datetime

  then follows the script

  i didn't understand the @@Startyear parameter.

  would any one say me what is the meaning of this and what situation do i need to use this type of parameter.

  Thanks in advance

  Regards,

  thaya

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  February 5, 2008 at 5:12 pm

  #775388

  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

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  February 5, 2008 at 5:42 pm

  #775390

  Nice job, Bill.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)