#1 2020-07-02 07:34:59

hx25523
Member
Registered: 2020-07-02
Posts: 5

How to call a Function in MS-SQL Server with Datasource Parameter

Hello,

I have created a Dynamic List which calls a Function in Microsoft SQL Server. Now, to this Dynamic List, I have created few Datasource parameters as the function accepts Six input parameters. Once we execute the report, User can select multiple values from each of the Datasource parameters but then if multiple values are selected, it results in the function having more arguments then what it can accept resulting in error.

Could someone advise please as to how can I can execute the function with multiple values to some of the parameters? Thanks.

Aiden

Offline

#2 2020-07-17 12:31:53

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,789
Website

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi hx25523,

I don't quite understand the issue. Why don't you create 6 single-select parameters as the function expects 6 arguments ?
Can you please post some screenshots? you can use http://imgbb.com for this.

Regards,
Eduardo

Offline

#3 2020-07-21 01:27:05

hx25523
Member
Registered: 2020-07-02
Posts: 5

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Eduardo,

Thank you for your response.

Let me try and explain this through a simple example. Hopefully, you can understand my question then.

Example:
Say I have a function called Test in MS-SQL, it accepts 3 parameters - start_date, end_date and product_id

The function accepts 3 parameters so I create 3 parameters in ReportServer accordingly. start_date and end_date are date parameters and product_id is a datasource parameter.

I call this function in ReportServer, the way I will call it is:
Select * from Test(${start_date},${end_date},$P!(product_id))

Now, when I execute this report in ReportServer, it expects me to pass parameters. I pass one value each for start and end date but for my product_id, I have multiple values to choose from so I choose about 3 values from the list or popup box as follows:

start_date: 2020-07-01
end_date: 2020-07-3
product_id: I choose 1,2 and 3 from the list of ids

After passing in the above values, when I run the report, it gives me an error. It says the function accepts 3 parameters but I am passing 5 parameters. So basically, the multiple values I pass for product_id, it does not allow me to create it as a single string of values to parse later, the value 2 that was passed, was considered as the 4th parameter and value 3 as fifth resulting in error.

I am looking at a solution like the $X{IN,...} but this works well with the Where clause, not when passing as an argument to a function.

Hope you understood my question? If yes, please advise of a solution, else will try and send images to explain.

Regards,
Aiden

P.S: Please note it works well if I pass single values to each of the expected parameter.

Offline

#4 2020-09-24 09:15:56

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,789
Website

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Aiden,

thanks for the details. We will look this in ticket RS-4373. I will update here when we have more information.

Regards,
Eduardo

Offline

#5 2020-09-28 00:10:02

hx25523
Member
Registered: 2020-07-02
Posts: 5

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Noted, thanks Eduardo!

Offline

#6 2020-09-30 12:05:09

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,789
Website

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Aiden,

a question: what would you expect exactly in your function when you select 1, 2, 3 ?

something like:

Select * from Test('start_date','end_date','1,2,3') ,

so you would expect a string containing '1,2,3' separated by commas ?

and what exactly do you get currently? I suppose something like:

Select * from Test('start_date','end_date',1,2,3)

is this correct? Can you pls check?

Regards,
Eduardo

Offline

#7 2020-09-30 23:25:16

hx25523
Member
Registered: 2020-07-02
Posts: 5

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Eduardo,

Thank you for your response!

That is correct, I expect this - Select * from Test('start_date','end_date','1,2,3') but end up getting this: Select * from Test('start_date','end_date',1,2,3) because of which a function that expects 3 parameters in this example results in having more then 3 parameters and it gives an error.

Regards,

Aiden

Offline

#8 2020-10-01 06:51:32

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,789
Website

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Aiden,

what happens if you write this query:

Select * from Test('start_date','end_date','$P!(product_id)')

note the ' ' surrounding the product_id parameter

Regards,
Eduardo

Offline

#9 2020-10-02 05:54:37

hx25523
Member
Registered: 2020-07-02
Posts: 5

Re: How to call a Function in MS-SQL Server with Datasource Parameter

Hi Eduardo,

I tried few things to find a solution but nothing worked.

With regards the query you mentioned, it gives the following error:

"The report could not be executed: Could not increment cursor on resultset."

Regards,

Aiden

Offline

Board footer

Powered by FluxBB