Use SET FMTONLY ON
to allow validating the parameter values without modifying data or returning rows. Then execute the procedure once for each parameter while setting its value to DEFAULT
, and all others to NULL
. An exception is returned for each non-optional parameter which can be handled by a catch block.
CREATE PROC GetContacts(@name int = NULL, @phone int, @email int)
AS RETURN
GO
SET FMTONLY ON
EXEC GetContacts DEFAULT, null, null
EXEC GetContacts null, DEFAULT, null
EXEC GetContacts null, null, DEFAULT
SET FMTONLY OFF
GO
/*
-- Results
Msg 201, Level 16, State 4, Procedure GetContacts, Line 0
Procedure or function 'GetContacts' expects parameter '@phone', which was not supplied.
Msg 201, Level 16, State 4, Procedure GetContacts, Line 0
Procedure or function 'GetContacts' expects parameter '@email', which was not supplied.
*/