Bom dia amigos,
Estou com essa questao por fazer e estou com dificuldade. Alguem saberia responde-la?
Create a stored procedure called ReorderQuantity to show when the reorder level subtracted from the units in stock is less than a specified value. The unit value will be an input parameter for the stored procedure. Display the ProductID, ProductName, UnitsInStock, and ReorderLevel from the Products table, and the supplier name from the Suppliers table. Sort by ProductName. Use the following query to test your stored procedure: EXEC [TermProject].[dbo].[ReorderQuantity] 5;
Isso foi o que fiz ate entao.
USE TermProject;GO DROP PROCEDURE IF EXISTS ReorderQuantity; GO CREATE PROCEDURE ReorderQuantity (@DIFF INT) AS SELECT P.[ProductID], P.[ProductName], S.[Name], P.[UnitsInStock], P.[ReorderLevel]
FROM [TermProject].[dbo].[Suppliers] S
JOIN (SELECT [SupplierID], [ProductID],[ProductName],[UnitsInStock],[ReorderLevel], SUM([ReorderLevel] - [UnitsInStock]) AS [SumOrderLevel]
FROM [TermProject].[dbo].[Products]
GROUP BY [ProductID]) AS P
ON P.[SupplierID] = S.[SupplierID]
WHERE P.[SumOrderLevel] < @DIFF
GROUP BY P.[ProductID]
ORDER BY P.[ProductName]GO
EXEC [TermProject].[dbo].[ReorderQuantity] 5;
Esse e o erro que da:
Msg 8120, Level 16, State 1, Procedure ReorderQuantity, Line 3 [Batch Start Line 26] Column 'P.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 2812, Level 16, State 62, Line 42 Could not find stored procedure 'TermProject.dbo.ReorderQuantity'.
