CODEWITHSUNDEEP
sqlsql-serverdatabasepostgresql
Arvind Singh
Arvind Singh

Reputation: 15

How can we pass null parameter into SQL Function?

Hi all I have created a scaler function in which I want pass parameter @aCOURSENAME as null but when I try to execute it without parameter it gives error of insufficient parameter. why it happening although I am passing null as parameter into function

Here is my function

CREATE FUNCTION UFN_XX_GETSTUCOUNT_ONCOURSE(@aCOURSENAME VARCHAR(20) = NULL)
RETURNS INT
AS
BEGIN 

    RETURN (SELECT  COUNT(SID) AS STUDENT  FROM BATCH B  
            INNER JOIN ENROLLMENT E 
            ON B.BATCHID = E.BATCHID
            INNER JOIN COURSE C 
            ON C.COURSEID = B.COURSEID
            WHERE COURSENAME = @aCOURSENAME
            GROUP BY COURSENAME)

END 

GO

SELECT DBO.UFN_XX_GETSTUCOUNT_ONCOURSE()

without parameter when I execute it gives error of insufficient parameter.

Upvotes: 1

Views: 4184

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56745

You cannot omit parameters when calling user-defined SQL functions(*). Yes, I know that stinks, and I have no idea why that is (probably some legacy decision in the SQL parser made 30 years ago). Nonetheless you either have to pass NULL explicitly:

SELECT DBO.UFN_XX_GETSTUCOUNT_ONCOURSE(NULL)

or "implicitly" using DEFAULT:

SELECT DBO.UFN_XX_GETSTUCOUNT_ONCOURSE(DEFAULT)

And yes, we all hate this and have been complaining about it for decades.

(* -- annoyingly, some system functions do allow optional arguments)

Upvotes: 3

Related Questions