/*
CREATE TABLE Employees (
	EmployeeId uniqueidentifier,
	LastName nvarchar(50),
	FirstName nvarchar(50),
	Email nvarchar(50)
)
GO
INSERT INTO Employees SELECT NEWID(), N'Rodman', N'John', N'jrodman@adatum.com'
INSERT INTO Employees SELECT NEWID(), N'Bossard', N'David', N'dbossard@adatum.com'
INSERT INTO Employees SELECT NEWID(), N'Aaberg', N'Jesper', N'jaaberg@adatum.com'
INSERT INTO Employees SELECT NEWID(), N'Halberg', N'Pernille', N'phalberg@adatum.com'
INSERT INTO Employees SELECT NEWID(), N'Haas', N'Jonathan', N'jhass@adatum.com'
GO
-- SELECT * FROM Employees
*/

EXECUTE sp_executesql N'SELECT TOP 10 * FROM Employees'
GO

DECLARE @sql nvarchar(max)
DECLARE @email nvarchar(50)
SET @email = N'phalberg@adatum.com'
SET @sql = 'EXEC GetUserByEmail N''' + @email + ''''
--SELECT @sql 
EXECUTE sp_executesql @sql 

GO

/*
CREATE PROC GetUserByEmail (
	@email nvarchar(50)
)
AS
SELECT * FROM Employees WHERE Email = @email
GO
*/

DECLARE @sql nvarchar(max)
DECLARE @email_input nvarchar(50)
SET @email_input = N'phalberg@adatum.com'
SET @sql = 'EXEC GetUserByEmail @email'
EXECUTE sp_executesql @sql, N'@email nvarchar(50)', @email = @email_input

GO

DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N'
	SELECT * FROM dbo.Employees;
	SELECT 
		@Email_out = Email
	FROM dbo.Employees
	WHERE 
		EmployeeId = @EmployeeId_input'

SET @Parameter_Definition = N'
	@EmployeeId_input uniqueidentifier, 
	@Email_out nvarchar(50) OUTPUT'

DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)

SET @EmployeeId = '3E8E578C-6810-48BD-AADB-620EDECF988C'

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT

SELECT @Email as Email

GO

/*
CREATE PROC GetEmail
(
	@EmployeeId_input uniqueidentifier,
	@Email_out nvarchar(50) OUTPUT
)
AS

SELECT * FROM dbo.Employees;

SELECT 
	@Email_out = Email
FROM dbo.Employees
WHERE 
	EmployeeId = @EmployeeId_input;
GO
*/

DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N'EXEC GetEmail @EmployeeId_input, @Email_out OUTPUT'

SET @Parameter_Definition = N'
	@EmployeeId_input uniqueidentifier, 
	@Email_out nvarchar(50) OUTPUT'

DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)

SET @EmployeeId = '00FCEFA4-BF81-4674-81C1-C2DE86F0C5F6'

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT

SELECT @Email as Email

GO

/*
CREATE PROC GetEmail2
(
	@EmployeeId uniqueidentifier,
	@Email nvarchar(50) OUTPUT
)
AS

SELECT * FROM dbo.Employees;

SELECT 
	@Email = Email
FROM dbo.Employees
WHERE 
	EmployeeId = @EmployeeId;
GO
*/
DECLARE @SQL_String NVARCHAR(max)
DECLARE @Parameter_Definition NVARCHAR(max)

SET @SQL_String = N'
	EXEC GetEmail2 @EmployeeId = @EmployeeId_input, @Email = @Email_out OUTPUT
'

SET @Parameter_Definition = N'
	@EmployeeId_input uniqueidentifier, 
	@Email_out nvarchar(50) OUTPUT'

DECLARE @EmployeeId uniqueidentifier
DECLARE @Email nvarchar(50)

SET @EmployeeId = '997B3351-F876-414B-9C63-B90EC967B69B'

EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT

SELECT @Email as Email

GO