[mihaispr]

Hi everyone and thanks for reading my question! This is my first post on this forum!

I have a project on a database called ParcAuto that has 4 tables inside with fields.

I generated the database and the 4 tables with columns and 6lines per table with this new query in SQL Server Management Studio Software:

-- creating database

CREATE DATABASE ParcAuto


-- first table


CREATE TABLE PatrimoniuMasini
(
IdMasina int PRIMARY KEY NOT NULL IDENTITY(1,1),
NumarMasina varchar(255) UNIQUE,
CapacitateMotor int,
CapacitateTransport int,
DataInregistrarii datetime,
PersoanaCeFoloesteMasina varchar(255)
)




-- second table

CREATE TABLE TipuriAutomobile
(IdTip int PRIMARY KEY NOT NULL,
IdMasina int,
TipAutomobil varchar(255)
)


-- third table

CREATE TABLE TipuriCombustibil
(IdCombustibil int PRIMARY KEY NOT NULL,
IdMasina int,
TipCombustibil varchar(255)
)




-- fourth table

CREATE TABLE FoaieParcursMasina
(IdFoaie int PRIMARY KEY NOT NULL,
IdMasina int,
DataOraPlecare datetime,
DataOraSosire datetime,
NumeSofer varchar(255),
LocalitatePlecare varchar(255),
LocalitateSosire varchar(255),
KM int,
CombustibilConsumat int,
MotivDeplasare varchar(255),
MarfaTransportata varchar(255),
DataInregistrarii datetime
)


-- inserting 6lines in the 4 tables


INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('PH-20-RTS',300, 5000, '09/03/09', 'Michel Sardou');
INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('B-03-STR',400, 5200, '12/30/07', 'Johann Gourcouff');
INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('IS-02-SPR',400, 7200, '10/10/08', 'Mireille Mathieu');
INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('CJ-02-MIH',200, 9100, '11/20/90', 'Mireille Labelle');
INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('CT-06-ION',230, 5100, '06/24/85', 'Mathieu Lafont');
INSERT INTO PatrimoniuMasini(NumarMasina,CapacitateMotor,CapacitateTransport,DataInregistrarii,PersoanaCeFoloesteMasina)
VALUES ('GL-05-TIZ',250, 6200, '07/24/99', 'Joel Abati');




INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(1,1,'masina');
INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(2,2,'auto-cisterna');
INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(3,3,'camion');
INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(4,4,'auto-utilitara');
INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(5,5,'4x4')
INSERT INTO TipuriAutomobile(IdTip,IdMasina,TipAutomobil)
VALUES(6,6,'masina')




INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(1,1,'benzina');
INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(2,2,'motorina');
INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(3,3,'motorina');
INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(4,4,'GPL');
INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(5,5,'benzina');
INSERT INTO TipuriCombustibil(IdCombustibil,IdMasina,TipCombustibil)
VALUES(6,6,'GPL')





INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(1,1,'02/12/07 17:32:35','02/12/07 18:51:42','Michel Sardou','Metz','Paris',120,20,'transport marfa','mere','02/12/07 17');
INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(2,2,'11/21/07 19:32:01','11/21/07 20:43:32','Johann Gourcouff','Rouen','Paris',100,17,'transport marfa','visine','11/21/07');
INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(3,3,'07/15/08 19:57:02','07/15/08 21:49:41','Mireille Mathieu','Paris','Montpeliier',140,31,'transport marfa','caise','07/15/08');
INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(4,4,'05/26/08 19:49:03','05/26/08 21:32:10','Mireille Labelle','Rouen','Montpeliier',59,12,'transport marfa','cirese','05/26/08');
INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(5,5,'06/24/09 20:29:49','06/24/09 21:39:29','Mathieu Lafont','Montpellier','Paris',140,31,'transport marfa','pere','06/24/09');
INSERT INTO FoaieParcursMasina(IdFoaie,IdMasina,DataOraPlecare,DataOraSosire,NumeSofer,LocalitatePlecare,LocalitateSosire,KM,CombustibilConsumat,MotivDeplasare,MarfaTransportata,DataInregistrarii)
VALUES(6,6,'08/04/09 20:32:46','08/04/09 22:15:29','Mathieu Lafont','Bordeaux','Paris',90,25,'transport marfa','gutui','08/04/09');




-- see the structure of columns and lines from the 4 tables

SELECT * FROM PatrimoniuMasini

SELECT * FROM TipuriAutomobile

SELECT * FROM TipuriCombustibil

SELECT * FROM FoaieParcursMasina


-

You can copy this in order to generate my tables with columns and 6lines per tables.

Or simply download the full file from here it's faster and simply execute the sql.file:

http://www.4shared.c...0d/4tables.html


My problem is that this stored procedure called Informations is done correctly regarding this 5 parameters: but which method is the best to display informations on this 5 parameters:

Method1:

CREATE PROC Informations -- creation procedure stoque
-- main
@IdMasina int,
@DataOraPlecare datetime,
@DataOraSosire datetime,
@LocalitatePlecare varchar(255),
@LocalitateSosire varchar(255)
AS

 
SELECT IdMasina,DataOraPlecare,DataOraSosire,LocalitatePlecare,LocalitateSosire
FROM FoaieParcursMasina
WHERE IdMasina = @IdMasina AND DataOraPlecare = @DataOraPlecare AND DataOraSosire = @DataOraSosire AND LocalitatePlecare = @LocalitatePlecare AND LocalitateSosire = @LocalitateSosire

EXEC Informations but I don't know the 5 values of my parameters how to call the stored procedure Informations.

Or Method2:

CREATE PROC Informations -- creation procedure stoque
-- main
@IdMasina int,
@DataOraPlecare datetime,
@DataOraSosire datetime,
@LocalitatePlecare varchar(255),
@LocalitateSosire varchar(255)
AS

 
SELECT IdMasina,DataOraPlecare,DataOraSosire,LocalitatePlecare,LocalitateSosire
FROM FoaieParcursMasina
 

WHERE DataOraPlecare = '07/15/08 19:57:02' AND DataOraSosire = '07/15/08 21:49:41' AND LocalitatePlecare='Paris' AND LocalitateSosire='Montpeliier'
EXEC Informations 3,'07/15/08 19:57:02','07/15/08 21:49:41','Paris','Montpeliier'

QUERY (The thing I want to do in my stored procedure)

The thing I want to do in this stored procedure is to make a stored procedure that displays informations about numberofcar(idmasina field), period(DataPlecarii,DataSosirii so date of departure and date of arrival in time) and Departure and Arrival as cities in my case is LocalitatePlecare or DepartureCity and LocalitateSosire or ArrivalCity).



My question is which of the 2 methods made my query above.


So I want to display a stored procedure with that 5 parameters but I don't know which stored procedure displays information about the 5 parameters correctly (Informations is a stored procedure with 5 parameters as you can see from code listed)

Any opinions will be really appreciated!

This post has been edited by mihaispr: 05 November 2009 - 08:51 AM

[Trogdor]

i dont really understand your question.
If you want to get some data back from your procedure you need to define output parameters.

example of output paramters:
@ErrorCode as Integer Output,
@ErrorDescription as VarChar(100) Output,

I always put them after input paramters, not sure if that is obligatory but it is more easy to read for sure.

then in the procedure you need to explicitly fill the output parameters:
SELECT @ErrorCode = 3, @ErrorDescription = 'The currency of the transaction does not match the currency of the request';
RETURN 0;
or dynamic:
SELECT @ErrorCode = IdMasina, @ErrorDescription = DataOraPlecare
FROM FoaieParcursMasina

oh, and you dont have any restriction on what data you are selecting
WHERE IdMasina = @IdMasina

Now selecting and returning a whole reccordset is again something different. If that is what you meant to do, ask again.


I do also see that you put the CapacitateMotor and CapacitateTransport in the first table. I guess that not all types of one brand have the same enginesize , so this data would better fit in the CarTypes table.

[mihaispr]

Yes you're right I've corrected that.

I want to integrate this query:

SELECT tab1.NumarMasina,tab2.DataOraPlecare,tab2.DataOraSosire,tab2.LocalitatePlecare,tab2.LocalitateSosire
FROM PatrimoniuMasini tab1
left join FoaieParcursMasina tab2 on tab1.IdMasina = tab2.IdMasina

In a stored procedure with 5 parameters.


I don't know why it executes me in a weird manner from 6 times my query in my stored procedure:

CREATE PROC Informations2  
@NumarMasina int,
@DataOraPlecare datetime,
@DataOraSosire datetime,
@LocalitatePlecare varchar(255),
@LocalitateSosire varchar(255)
AS

-- query
SELECT tab1.NumarMasina,tab2.DataOraPlecare,tab2.DataOraSosire,tab2.LocalitatePlecare,tab2.LocalitateSosire
FROM PatrimoniuMasini tab1
left join FoaieParcursMasina tab2 on tab1.IdMasina = tab2.IdMasina

-- executie procedura stocata
EXEC Informations2 3,'07/15/08 19:57:02','07/15/08 21:49:41','Paris','Montpeliier'

I don't understand how to made a stored procedure with those 5 paremeters (that displays information with this fields).

So my question is how to modify my stored procedure started from my query. In fact this is my problem. (also I don't know my first line it's NULL in the rest it's associating correctly as I wanted).

Here is the modified file:

Pls look at my file attached here:

http://www.4shared.c...d3/4tabele.html

This post has been edited by mihaispr: 05 November 2009 - 11:48 AM

[mihaispr]

I solved the procedure requesting some informations (and that has 5 parameters)

Thanks anyway!


Here's the code to help others that dealt a similar problem like me:

CREATE PROCEDURE PsInformatii
	@NumarMasina VARCHAR(255),
	@DataOraPlecare DATETIME,
	@DataOraSosire DATETIME,
	@LocalitatePlecare VARCHAR(255),
	@LocalitateSosire VARCHAR(255) 
AS
BEGIN
	SELECT tab1.NumarMasina,
			tab2.DataOraPlecare,
			tab2.DataOraSosire,
			tab2.LocalitatePlecare,
			tab2.LocalitateSosire
	FROM PatrimoniuMasini AS tab1
	LEFT JOIN FoaieParcursMasina AS tab2
		ON tab1.IdMasina = tab2.IdMasina
	WHERE tab1.NumarMasina = @NumarMasina
	AND tab2.DataOraPlecare = @DataOraPlecare
	AND tab2.DataOraSosire = @DataOraSosire
	AND tab2.LocalitatePlecare = @LocalitatePlecare
	AND tab2.LocalitateSosire = @LocalitateSosire
END
GO

-- executie procedura stocata cu completare anumiti parametri

EXEC PsInformatii 'DB-20-RTS','020207 09:32:35','020207 11:20:32','Targoviste','Bucuresti'