[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 ?

[abhijit]

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

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

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

try using the alias.

[techgnome]

I'm thinking that a few additional parenthesis might be in order:

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

If it was a table aliasing problem, it would be a different error.

-tg

[mihaispr]

Thaks for your reply!

Still cannot figure out how to solve my stored procedure

CREATE PROCEDURE ProcAgregare

@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

Errors:

Msg 207, Level 16, State 1, Procedure ProcAgregare, Line 21
Invalid column name 'IdTip'.
Msg 207, Level 16, State 1, Procedure ProcAgregare, Line 21
Invalid column name 'TipCombustibil'.
Msg 207, Level 16, State 1, Procedure ProcAgregare, Line 22
Invalid column name 'IdTip'.

Must I use an EXEC statement?

I need to make with avg function the average of combustible consumed(oil,gas etc.-contained in column CombustibilConsumat) group by the things I've done in a period (the period is expressed as you can see by the 2columns DataOraPlecare(StartDate which is datetime) and DataOraSosire(EndDate or DateArrival which is datetime).

EXEC ProcAgregare


I've posted the DDL for my 3 tables you can run them in sql server to see the columns of each table.

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 alises of my 3 tables called FoaieParcursMasina, PatrimoniuMasini and TipuriAutomobile.

[techgnome]

idTip is not in the table PatrimoniuMasini .... therefore tab2.idTip will fail....
Same with the other errors....
If you use meaningful aliases, it might make it easier.
FoaieParcursMasina AS FPM
PatrimoniuMasini AS PM
TipuriAutomobile AS TA

-tg

[mihaispr]

I've corrected the stored procedure:

CREATE PROCEDURE ProcAgregare

@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
	(tab3.IdTip = @TipAutomobil  AND tab3.TipCombustibil = @TipCombustibil)
GROUP BY tab3.IdTip,
	tab1.DataOraPlecare,
	tab1.DataOraSosire,
	tab3.TipAutomobil,
	tab3.TipCombustibil
END
GO

Effect:

Command(s) completed successfully.


How I can execute the stored procedure now?

I tried EXEC ProcAgregare and then I put the parameters ?

[techgnome]

if running it from something like Query Analyzer or SQL Management Studio, yes... if from code, then it's a little more complicated. And for that I suggest you read the Database FAQ And Tutorial thread.

-tg

[mihaispr]

if running it from something like Query Analyzer or SQL Management Studio, yes... if from code, then it's a little more complicated. And for that I suggest you read the Database FAQ And Tutorial thread.

-tg

Thank you! The thread is now solved!