IT Process Automation

Hi team,

Is anyone aware of how we should pass parameters to SQL server table-valued functions?

When doing a select, you can use "inline statement" with some "?" and provide the value for each parameters.

This inline statement, with a parameters, works perfectly fine.

When i try to do the same with a SQL function, PAM says : The value is not set for the parameter number 1.

what i'm trying is :

Select * from [table-valued_Function] (?,?,?,?)

And send the parameters as follow :

Coding the value in the inline statement works perfectly. (Select * from [table-valued_Function] ( 'test','test','test','test')

Each variable i used as a parameter have a value.

My function works well in SQL using the exact same syntax ( Select * from [table-valued_Function] (?,?,?,?) )

Anyone?

I've changed my approach to use a stored proc instead of a function and it works. But still, i want to know why it does not work

I believe when you have multiple parameters, the syntax to represent that parameter in the query is %1, %2.  Partly confusing because %1 would represent the parameter that is labeled 0 and %2 would represent the parameter labeled 1 and so on.

This was my first idea, but it does not work. If i call a stored proc instead of a function, you can use multiple ?. This syntax works :

EXEC [catalogDropdowns].[dbo].[S501_getEmailValues]
@comitee_eem_group = ?,
@rd_eem_group = ?,
@en_fr_function = ?,
@en_fr_line = ?

Looks like we'll need to ask PAM dev team