USE exercise GO CREATE PROC REPORT @customerID VARCHAR AS SELECT * INTO #temp FROM customer,animal WHERE customer.customerID = @customerID ALTER TABLE #temp ADD Printed SMALLINT UPDATE #temp SET Printed = 0 DECLARE @customerName VARCHAR (30) DECLARE @customerTelephone VARCHAR(30) DECLARE @animalID INT DECLARE @animalName VARCHAR (30) DECLARE @quantity INT DECLARE @price INT DECLARE @speciesName VARCHAR(40) DECLARE @totalPrice INT SELECT @customerName = customer.customerName, @customerTelephone = customer.customerTelephone FROM customer WHERE @customerID = customer.customerID PRINT 'CustomerID: ' +@customerID PRINT 'Customer Name ' +@customerName PRINT 'Customer Telephone: ' +@customerTelephone PRINT'animalID animalName quantity price Species totalPrice' WHILE EXISTS (SELECT * FROM #temp WHERE Printed = 0) BEGIN SELECT @animalID = MIN(animalID) FROM #temp WHERE Printed = 0 SELECT @animalName = animalName, @quantity = animalCustomer.quantity, @speciesName = species.specieName FROM animal INNER JOIN animalCustomer ON animal.animalID = animalCustomer.animalIdentificater INNER JOIN species ON species.specieName = animal.speciesIdentificater WHERE animal.animalID = @animalID SET @totalPrice = @price * @quantity PRINT @animalID+' '+@animalName+' '+@quantity+' '+@speciesName+' '+@totalPrice UPDATE #temp SET Printed = 1 WHERE @animalID = animalID END DROP TABLE #temp GO
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)