Stored procedure parameters: Setting default values with functions

Stored procedures are common objects in SQL Server databases as it gives many benefits for us. One important thing with Stored Procedure is, allowing us to pass parameters for executing the procedure as we want. Parameters can be set as either input or output parameters and input parameters can be set with default values allowing users to execute the procedure either with value they need or without passing a value, letting procedure to use the default value. However, can we set up a function for the default value? That is the question I was asked.

SQL Server allows us to pass 2,100 parameters per Stored Procedure. Parameters can be set only with constant expressions, hence functions CANNOT be used when setting default values to parameters. But we can workaround it, though it is not the exact solution;

USE AdventureWorks2014;
GO

IF OBJECT_ID('dbo.GetOrders', 'P') IS NOT NULL
 DROP PROCEDURE dbo.GetOrders;
GO

-- creating the procedure
-- note that default value is null
-- and set the default value if no value found
CREATE PROCEDURE dbo.GetOrders @Year int = NULL
AS
BEGIN

 SET @Year = ISNULL(@Year, YEAR(getdate()));

 print @Year
 SELECT * FROM Sales.SalesOrderHeader
 WHERE YEAR(OrderDate) = @Year;
END
GO

-- EXEC dbo.GetOrders