Procedure SQL SERVER

--if u want delete it
--DROP PROCEDURE sp_POIforCategory
--GO
--if u want change it use alter
/*CREATE PROCEDURE*/ ALTER PROCEDURE sp_POIforCategory
(
@nomecat VARCHAR(20)
)
AS
BEGIN
   -- SET NOCOUNT ON
   SELECT p.*
   FROM Poi AS P INNER JOIN Category AS C
   ON P.FK_IDCity = C.IDCategory
   WHERE C.categories = @nomecat

END
--end s

--Richiamo
EXEC sp_POIforCategory 'Restaurant'

-----------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE  sp_POIforCategoryAlternative
(
@nomecat VARCHAR(20)
)
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @idCat INT
   SET @idCat = (SELECT TOP 1 Category.IDCategory
                 FROM Category
                 WHERE categories = @nomecat)

    SELECT P.*
    FROM Poi AS P
    WHERE P.category = @idCat


END

EXEC sp_POIforCategoryAlternative 'Ciao'

----------------------------------------------------------------

CREATE PROCEDURE sp_repeat
(
@n INT,
@message VARCHAR(50)
)
AS
BEGIN
     DECLARE @i INT
     SET @i=0
     WHILE @i < @n
     BEGIN
         PRINT @message
         SET @i=@i+1
     END
END

-------------------------------------------------------------------

CREATE PROC sp_Category_Ins
(
@nomeCat VARCHAR(50)
)
AS
BEGIN
   IF NOT EXISTS(SELECT * FROM  Category WHERE categories=@nomeCat)
   BEGIN
   INSERT INTO Category(categories) VALUES(@nomeCat)

   DECLARE @newid INT
   SELECT @newid = @@IDENTITY
   PRINT 'Insert cat id=' + CONVERT(VARCHAR,@newid)
   END
   ELSE
   BEGIN
   PRINT 'Category already exist'
   END
END