Menu
If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter. To provide a default value to a parameter, you should use this format: "@parameter_name data_type = default_value".
Fortunately, it's pretty easy to make some parameters required and others optional. You simply give them a default value. The first three parameters are required and the next three aren't.
We can create function with default parameters. To call that function without that parameter, you need to pass NULL to that parameter. Let's say I have function with Two Parameters, in which second parameter can be optional. Let me know if it helps you in any way.
you have to call it like this
SELECT dbo.CheckIfSFExists(23, default)
From Technet:
When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behaviour is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. An exception to this behaviour is when invoking a scalar function by using the EXECUTE statement. When using EXECUTE, the DEFAULT keyword is not required.
You can call it three ways - with parameters, with DEFAULT and via EXECUTE
SET NOCOUNT ON;
DECLARE
@Table SYSNAME = 'YourTable',
@Schema SYSNAME = 'dbo',
@Rows INT;
SELECT dbo.TableRowCount( @Table, @Schema )
SELECT dbo.TableRowCount( @Table, DEFAULT )
EXECUTE @Rows = dbo.TableRowCount @Table
SELECT @Rows
With user defined functions, you have to declare every parameter, even if they have a default value.
The following would execute successfully:
IF dbo.CheckIfSFExists( 23, default ) = 0
SET @retValue = 'bla bla bla;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With