[RESOLVED] Stored procedure little errors
Hi everyone and thanks for reading my question!
I have 3 tables I give you the DDL for the 3:
Code:
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)
Code:
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:
Code:
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@DataOraPlecare".
I need to replace this:
Code:
@DataOraPlecare datetime,
@DataOraSosire datetime,
With another names of variables to not make a confusion with the column names DataOraPlecare and DataOraSosire?
Like this:
Code:
@StartDate datetime,
@EndDate datetime,
And according to that clause where will be modified like this:
Code:
WHERE (DataOraPlecare >= @StartDate AND DataOraPlecare <= @EndDate)
AND
(DataOraSosire >= @StartDate AND DataOraSosire <= @EndDate)
I'm confused ... :( do you have any ideas ?
Re: Stored procedure little errors
Quote:
Originally Posted by
mihaispr
And according to that clause where will be modified like this:
Code:
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.
Re: Stored procedure little errors
I'm thinking that a few additional parenthesis might be in order:
Code:
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
Re: Stored procedure little errors
Thaks for your reply!
Still cannot figure out how to solve my stored procedure:eek2:
Code:
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:
Code:
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.
Code:
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.
Re: Stored procedure little errors
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
Re: Stored procedure little errors
I've corrected the stored procedure:
Code:
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 ?
Re: Stored procedure little errors
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
Re: Stored procedure little errors
Quote:
Originally Posted by
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
Thank you! The thread is now solved!
:thumb:
