[RESOLVED] Stored procedure little errors

[mihaispr]

Hi everyone and thanks for reading my question!

I have 3 tables I give you the DDL for the 3:

CREATE DATABASE ParcAuto

CREATE TABLE PatrimoniuMasini
(
IdMasina int PRIMARY KEY NOT NULL IDENTITY(1,1),
NumarMasina varchar(255) UNIQUE,
PersoanaFolMasina varchar(255),
Utilizator varchar(255)
)


-- utilizator e firma careia ii apartine automobilul sau l-a inchiriat

-- tabela2 TipuriAutomobile

CREATE TABLE TipuriAutomobile
(IdTip int PRIMARY KEY NOT NULL,
IdMasina int,
TipAutomobil varchar(255),
CapacitateMotor varchar(255),
CapacitateTransport varchar(255),
TipCombustibil varchar(255),
NormaL100KM decimal(3,2) -- 3 zecimale in total , 2 zecimale dupa virgula
)



-- un numar de masina poate face mai multe drumuri

CREATE TABLE FoaieParcursMasina
(IdFoaie int PRIMARY KEY NOT NULL,
DataFoaieParcurs datetime,
IdMasina int,
NumarMasina varchar(255),
DataOraPlecare datetime,
DataOraSosire datetime,
NumeSofer varchar(255),
LocalitatePlecare varchar(255),
LocalitateSosire varchar(255),
KM int,
CombustibilConsumat int, --combustibil consumat =km*normaL100km
MotivDeplasare varchar(255),
MarfaTransportata varchar(255),
Utilizator varchar(255)
)

tab1,tab2 and tab3 are aliases for my 3 tables.



Here is the full stored procedure: (starting from the DDL of 3 tables gave above)

CREATE PROCEDURE ProcJoinEtAgregat
@DataOraPlecare datetime,
@DataOraSosire datetime,
@TipAutomobil varchar(255),
@TipCombustibil varchar(255)
 
AS
BEGIN
 
SELECT tab3.IdTip,AVG(CombustibilConsumat) AS avgCombustibilConsumat
FROM dbo.FoaieParcursMasina AS tab1
	INNER JOIN dbo.PatrimoniuMasini AS tab2
		ON tab1.IdMasina = tab2.IdMasina
	INNER JOIN dbo.TipuriAutomobile AS tab3
		ON tab2.IdMasina = tab3.IdMasina

WHERE (DataOraPlecare >= @DataOraPlecare AND DataOraPlecare <= @DataOraSosire)
	AND
	(DataOraSosire >= @DataOraPlecare AND DataOraSosire <= @DataOraSosire)
	AND
	(tab2.IdTip = @TipAutomobil  AND tab2.TipCombustibil = @TipCombustibil)

GROUP BY tab2.IdTip,
	tab1.DataOraPlecare,
	tab1.DataOraSosire,
	tab3.TipAutomobil,
	tab3.TipCombustibil

END
GO

I made this stored procedure starting from the structure of my 3 tables but I don't know how I can modify the stored procedure. The columns are correctly used just need a help to correct my syntax errors.

Error:

Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@DataOraPlecare".

I need to replace this:

@DataOraPlecare datetime,
@DataOraSosire datetime,

With another names of variables to not make a confusion with the column names DataOraPlecare and DataOraSosire?

Like this:

@StartDate datetime,
@EndDate datetime,

And according to that clause where will be modified like this:

WHERE (DataOraPlecare >= @StartDate AND DataOraPlecare <= @EndDate)
	AND
	(DataOraSosire >= @StartDate AND DataOraSosire <= @EndDate)

I'm confused ... do you have any ideas ?