USE AdventureWorks;
GO
SELECT PM.Name, OA.Ilosc FROM Production.ProductModel AS PM
OUTER APPLY 
(
	SELECT COUNT(P.ProductID ) AS Ilosc FROM Production.Product AS P
	WHERE PM.ProductModelID = P.ProductModelID 
) AS OA;
GO

 USE AdventureWorks;
  GO
  CREATE PROCEDURE ModyfikujPodkategorieProduktu
  (
	@ID INT,
	@PCID INT,
	@NAZWA NVARCHAR(50),
	@ROWGUID UNIQUEIDENTIFIER,
	@DATA DATETIME 
  )
  AS
  BEGIN
	UPDATE Production.ProductSubcategory
	SET ProductCategoryID = @PCID,
		Name = @NAZWA,
		ROWGUID = @ROWGUID,
		ModifiedDate = @DATA
	WHERE ProductSubcategoryID = @ID;
  END;
  GO
  CREATE PROCEDURE DodajKategorieProduktu
  (
	@NAZWA NVARCHAR(50),
	@PCID INT
  )
  AS
  BEGIN
  INSERT INTO Production.ProductSubcategory
           (ProductCategoryID
           ,Name
           ,rowguid
           ,ModifiedDate)
     VALUES
           (
            @PCID
           ,@NAZWA
           ,NEWID()
           ,CURRENT_TIMESTAMP
           );

  SELECT SCOPE_IDENTITY() AS [NewID];
  END;
  GO
  CREATE PROCEDURE UsunPodkategorieProduktu
  (
	@ID INT
  )
  AS
  BEGIN
	DELETE FROM Production.ProductSubcategory
	WHERE ProductSubcategoryID = @ID;
  END;
GO

USE AdventureWorks;
GO
SELECT * FROM Production.ProductSubcategory AS PS
WHERE PS.Name = 'Testowa podkategoria produktu';
GO
SELECT * FROM Production.ProductSubcategory AS PS
WHERE PS.Name = 'Testowa podkategoria produktu - modyfikacja';
GO

USE AdventureWorks;
GO
CREATE PROCEDURE PobierzNProduktow
(
	@n INT
)
AS
BEGIN
	SELECT TOP(@n) * FROM Production.Product;
END;



