Call a Function with varied number of parameters from a stored Procedure

1

Dear Expert,

I am calling a Function say TestFunc from a Stored procedure. The function named TestFunc in one Database might have one parameter while in other Database the Function Named TestFunc might have two parameters. So, i have tried to call the function TestFunc in a Stored procedure as below so as to make the common Stored Procedure for all database.

Declare @Version NUMERIC(2)
DECLARE @SQL NVARCHAR(MAX)

SELECT @Version=LEFT(REPLACE (MAX(SCMBVERSION) , '.' , ''),2) FROM YSYNC

SET @SQL='IF @Version>50 '+
'SELECT DBO.GetBalAmt(''00001004'',0) '+
' ELSE '+
'SELECT DBO.GetBalAmt(''00001004'') '

EXEC SP_EXECUTESQL @SQL, N'@Version NUMERIC(2) OUT', @Version OUT

But while executing this, I got an error Procedure or function DBO.GetAvailBalAmt has too many arguments specified. Please suggest how can I achieve this. Thanks in advance.

Regards,
Rmt

2

Parser is probably barfing on the unused one ...

SET @SQL=
	'SELECT DBO.GetBalAmt(''00001004'''
	+ CASE WHEN @Version>50 THEN ',0' ELSE '' END
	+ ') '

should do the trick.SP_EXECUTESQL won't need the @Version parameter (not sure why you have it as an OUT parameter??)

1 Like
3

Dear Expert,

Thank you so much for the Trick. It worked.. Indeed a great Trick!!