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