USE
	TSQLforDev;
GO
IF OBJECT_ID('Marketing.usp_CustomersByCountry') IS NOT NULL
	DROP PROCEDURE Marketing.usp_CustomersByCountry
GO

SET STATISTICS IO ON;
GO


-- Create the "Marketing.usp_CustomersByCountry" stored procedure

CREATE PROCEDURE
	Marketing.usp_CustomersByCountry
(
	@Country AS NCHAR(2)
)
AS

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO


-- Parameterized Procedure:
-- Execute the "Marketing.usp_CustomersByCountry" stored procedure with the parameter "IL"

EXECUTE Marketing.usp_CustomersByCountry
	@Country = N'IL';
GO

-- Local Variable:
-- Execute the query directly with country "IL"

DECLARE
	@Country AS NCHAR(2) = N'IL';

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO



EXECUTE Marketing.usp_CustomersByCountry
	@Country = N'CN';

GO



EXECUTE Marketing.usp_CustomersByCountry
	@Country = N'CN' WITH RECOMPILE;

GO



-- Parameterized Ad-Hoc Procedure:
-- Execute the query dynamically with country "IL"
DECLARE
	@Country	AS NCHAR(2)			= N'IL' ,
	@QueryText	AS NVARCHAR(MAX) ,
	@Parameters	AS NVARCHAR(MAX);

SET @QueryText =
	N'
		SELECT
			Id ,
			Name ,
			LastPurchaseDate
		FROM
			Marketing.Customers
		WHERE
			Country = @pCountry;
	';

SET @Parameters = N'@pCountry AS NCHAR(2)';

EXECUTE sys.sp_executesql
	@statement	= @QueryText ,
	@params		= @Parameters ,
	@pCountry	= @Country;
GO

-- Local Variable with OPTION(RECOMPILE):
-- Execute the query directly with country "IL"

DECLARE
	@Country AS NCHAR(2) = N'IL';

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country
OPTION (RECOMPILE);
GO


IF OBJECT_ID('Marketing.usp_CustomersByCountry_2') IS NOT NULL
	DROP PROCEDURE Marketing.usp_CustomersByCountry_2
GO

-- Create the "Marketing.usp_CustomersByCountry_2" stored procedure

CREATE PROCEDURE
	Marketing.usp_CustomersByCountry_2
(
	@Country AS NCHAR(2)
)
AS
DECLARE @CountryLocal NCHAR(2)

SET @CountryLocal = @Country;

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @CountryLocal;
GO


EXECUTE Marketing.usp_CustomersByCountry_2
	@Country = N'IL';
GO


-- Create the "Marketing.usp_CustomersByCountry_2" stored procedure with OPTION(RECOMPILE)

ALTER PROCEDURE
	Marketing.usp_CustomersByCountry_2
(
	@Country AS NCHAR(2)
)
AS
DECLARE @CountryLocal NCHAR(2)

SET @CountryLocal = @Country;

SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @CountryLocal
OPTION (RECOMPILE);
GO


EXECUTE Marketing.usp_CustomersByCountry_2
	@Country = N'IL';
GO


IF OBJECT_ID('Marketing.usp_CustomersByCountry_3_small') IS NOT NULL
	DROP PROCEDURE Marketing.usp_CustomersByCountry_3_small
GO
CREATE PROCEDURE
	Marketing.usp_CustomersByCountry_3_small
(
	@Country AS NCHAR(2)
)
AS
SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO

IF OBJECT_ID('Marketing.usp_CustomersByCountry_3_big') IS NOT NULL
	DROP PROCEDURE Marketing.usp_CustomersByCountry_3_big
GO
CREATE PROCEDURE
	Marketing.usp_CustomersByCountry_3_big
(
	@Country AS NCHAR(2)
)
AS
SELECT
	Id ,
	Name ,
	LastPurchaseDate
FROM
	Marketing.Customers
WHERE
	Country = @Country;
GO

IF OBJECT_ID('Marketing.usp_CustomersByCountry_3_main') IS NOT NULL
	DROP PROCEDURE Marketing.usp_CustomersByCountry_3_main
GO

CREATE PROCEDURE
	Marketing.usp_CustomersByCountry_3_main
(
	@Country AS NCHAR(2)
)
AS

IF @Country IN ('US', 'CN', 'UK')
	EXEC Marketing.usp_CustomersByCountry_3_big @Country
ELSE
	EXEC Marketing.usp_CustomersByCountry_3_small @Country

GO


EXEC Marketing.usp_CustomersByCountry_3_main @Country = 'IL';



EXEC Marketing.usp_CustomersByCountry_3_main @Country = 'CN';

GO

