T-SQL - function with default parameters

Question

I have this script   CREATE FUNCTION dbo CheckIfSFExists  param1 INT   param2 BIT   1   RETURNS BIT AS BEGIN     IF EXISTS   bla bla bla           RETURN 1      RETURN 0  END GO   I want to use it in a  procedure in this way   IF dbo CheckIfSFExists  23     0     SET  retValue    bla bla bla     But I get the error      An insufficient number of arguments were supplied for the procedure or function dbo CheckIfSFExists    Why does it not work

User · Accepted Answer

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

User · Answer

One way around this problem is to use stored procedures with an output parameter   exec sp mysprocname  returnvalue output   firstparam   1   secondparam 2  values you do not pass in default to the defaults set in the stored procedure itself   And you can get the results from your output variable

User · Answer

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

User · Answer

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

[tsql] T-SQL - function with default parameters

Examples related to tsql

Examples related to function

Examples related to default-parameters