Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing parameter Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2008 3:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 12, 2010 11:09 PM
Points: 15, Visits: 44
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
Post #451935
Posted Tuesday, February 5, 2008 5:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 12:12 PM
Points: 3,248, Visits: 569
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



Post #451960
Posted Tuesday, February 5, 2008 5:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #451962
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse