Luis Cazares (12/3/2013)
The second option is to default the values to NULL and assign values dinamically.
CREATE PROCEDURE MyProcedure(@Param1 int = NULL,
@Param2 varchar(50) = NULL
)
AS
BEGIN
IF @Param1 IS NULL AND @Param2 IS NULL
BEGIN
--Code to assign default values
END
--Code for SP
END
I don't recommend this one because it messes with the optimiser's ability to sniff parameter values for cardinality estimates.
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability