nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How Do I Execute a Stored Procedure in BIRT and Pass the Value of a Multi-select Parameter to the S

kpelzer29

Is there a way I can execute a stored procedure in BIRT and pass in the value of a multi-select report parameter? Thank you!

Find more posts tagged with

Comments

mwilliams

To use a multi-select parameter, you'll probably have to make the object into a comma separated string (or however you send it through your stored procedure) with myParameter = params["myParameter"].join(",") in your beforeOpen script of your dataSet and then use it how you need it in your query by modifying your this.queryText. Hope this helps. Let me know if you have any questions!

kpelzer29

Hi mwilliams,

I tried making the object a comma seperated string and passing the value to the stored procedure.

In the beforeOpen script of the dataset:
this.queryText = this.queryText.replace("myParameterList", params["myParameter"].join(","));

Is this what you had in mind? When I run the dataset, I get the following error message:
SQL error #1: Incorrect syntax near ','.

Hans_vd

Hi kpelzer29, 
 
You need to surround that parameter value by quotes; something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("myParameterList", "'" + params["myParameter"].join(",")) + "'";</pre>
 
Hope this helps 
Hans

mwilliams

Sorry for not being clear with my response. I was just being generic about the join statement! Thanks for clearing this up for me Hans!

kpelzer29

Thank you mwilliams and Hans! 
 
I tried using the code Hans provided and I am still getting errors. Would it be possible to show me an example of how to use the variable in the stored procedure and how to pass in the multi-select report parameter? 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="91451" data-time="1323705236" data-date="12 December 2011 - 08:53 AM">
Sorry for not being clear with my response. I was just being generic about the join statement! Thanks for clearing this up for me Hans! </blockquote>

mwilliams

What is the error you're getting? Let's check what you're passing through as your stored procedure. Maybe you'll see the error in the formatting this way.

In your initialize event of your report, pu:

QT = "";

In your beforeOpen event of your dataSet, after all other script, put:

QT = this.queryText;

Now, in a dynamic textbox in your design, put:

QT;

Make sure this text box is after an element bound to your dataSet, so that the dataSet is ran. This should show you in your design what you're putting through to your stored procedure.

kpelzer29

The dynamic textbox is displaying this value for the queryText: 
EXEC dbo.testStoredProcedure @param1 = ?, @param2 = "val1,val2" 
 
(where val1 and val2 are values in the database) 
 
The error message I get when I run the report is: 
SQL statement does not return a ResultSet object. 
SQL error#1: Incorrect syntax near 'val1'. 
 
I am inserting the value of the parameter in the WHERE clause of the stored procedure using the IN statement: 
IN (@param2) 
 
Is there something I should be doing differently in the queryText in BIRT or the stored procedure in the database? Thank you! 
 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="91528" data-time="1323798150" data-date="13 December 2011 - 10:42 AM">
What is the error you're getting? Let's check what you're passing through as your stored procedure. Maybe you'll see the error in the formatting this way. 
 
In your initialize event of your report, pu: 
 
QT = ""; 
 
In your beforeOpen event of your dataSet, after all other script, put: 
 
QT = this.queryText; 
 
Now, in a dynamic textbox in your design, put: 
 
QT; 
 
Make sure this text box is after an element bound to your dataSet, so that the dataSet is ran. This should show you in your design what you're putting through to your stored procedure. </blockquote>

mwilliams

Sorry, I thought the script above had "','" in the join. You'll need to do that. Your query text should probably look like:

EXEC dbo.testStoredProcedure @param1 = ?, @param2 = 'val1','val2'

So, change the join(",") to join("','") and make sure you get the first and last ticks added as well. Let me know.

kpelzer29

mwilliams,

I changed the join so that now the query text looks like this:

EXEC dbo.testStoredProcedure @param1 = ?, @param2 = "val1','val2"

I still get the error:
SQL statement does not return a ResultSet object.
SQL error #1:Incorrect syntax near 'val1'.

mwilliams

Can you show the code you're using to make this? Thanks!

Hans_vd

I see lots of quotes everywhere.
But I think - didn't test it - that your querytext should look like this:

EXEC dbo.testStoredProcedure @param1 = ?, @param2 = 'val1,val2'

Hope this helps
Hans

kpelzer29

Thank you for the suggestion. I changed the query text to be: 
this.queryText = this.queryText.replace("myParameterList", '' + params["myParameter"].join(",") + ''); 
QT = this.queryText; 
 
The dynamic textbox now displays: 
EXEC dbo.testStoredProcedure @param1 = ?, @param2 = 'val1,val2' 
 
But I get this error message: 
SQL statement does not return a ResultSet object. 
SQL error #1: Procedure or function testStoredProcedure has too many arguments specified. 
 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="91732" data-time="1323855628" data-date="14 December 2011 - 02:40 AM">
I see lots of quotes everywhere. 
But I think - didn't test it - that your querytext should look like this: 
 
EXEC dbo.testStoredProcedure @param1 = ?, @param2 = 'val1,val2' 
 
Hope this helps 
Hans </blockquote>

mwilliams

If you put in static values in your query, what format works to return data? Rather than running the replace in script, can you figure out what format actually returns data for you by hard coding values? Let us know.