Equivalent of Stored Procedure Logic

building semantic layer
1

Hi,

Following is the MS SQL stored procedure syntax

BEGIN

declare @tvarno varchar(100)
set @tvarno= @tno
create table #tno (tno int)
WHILE (charindex(’,’, @tvarno)) <> 0
begin
SET @tno = convert(int,substring(@tvarno, 1, (charindex(’,’, @tvarno)-1)))
SET @tvarno = REPLACE(@tvarno, convert(varchar,@tno)+’,’, ‘’)
insert into #tno values(@tno)
end
select @tno=convert(int,@tvarno)
insert into #tno values(@tno)

I have to collect these tno’s & need to show on the reports.

How to implement above logic using BO universe where @tno is the parameter/prompt in which users are going enter the values.

Thanks in Advance

patneel :india: (BOB member since 2006-11-02)

2

You cannot really use Stored Procs in XI R2 Designer. There is a way to use Stored Procs using SQL Server’s OpenRowset function but it is not very reliable (at least for me).

For your requirement, I think you can put all your logic in SQL Server’s table valued UDF and use this UDF in designer. You can join the UDF to other tables as well. The only thing is you cannot use temp tables but can use table variables. Again, table variables is not a great great idea for huge results set but give it a shot and see if it works for you.

sunjer (BOB member since 2008-01-30)

3

Maybe I’m missing something obvious but I can’t see table valued functions that are in my database in the list of available “tables” in the Table Browser window that is shown when I select Insert…Table in Designer. How are you suggesting that you could use this UDF in designer?

I suppose I could create a derived table that is simply

SELECT * FROM UDF(parm1, parm2)

but that seems to introduce unnecessary abstraction/overhead.

mkrigba :us: (BOB member since 2006-11-22)

4

Hi,
I have the same issue. I do not see function in the table browser list.
How can i call the function in the universe?

Thanks for all your help.

tsr_bo (BOB member since 2008-06-13)

5

Sorry, I mean User defined function…

tsr_bo (BOB member since 2008-06-13)

6

Step back a second.

Is this a comma separated string that you need to get all values for?

If so, have a search for it, myself and others have provided solutions for this in the past.

7

While this post may have started with a requirement to parse a comma separated string, it has evolved based on a suggested solution to utilize functions. This is a more generic desire to include table valued user defined functions (AKA parameterized views) as tables in a universe.

mkrigba :us: (BOB member since 2006-11-22)

8

User defined functions are defined at the individual level within Deski/Reporter, not at the universe.

Creating a function and using a derived table to display it may work. R&D time for someone.

9

Calling UDFs from Designer discussed here:-

https://bobj-board.org/t/129685

Mak 1 :uk: (BOB member since 2005-01-06)

10

I am able to use the UDF but how can i pass the user entered prompt value to the UDF.

I am calling the UDF to initiate the stored procedure that in turn saves the data in temp tables and i will develop objects on temp tables.
But iam not able to pass the user entered prompt value to the function.

Please help me

tsr_bo (BOB member since 2008-06-13)

11

A serach for udf and prompt yielded this:-

https://bobj-board.org/t/121607

Mak 1 :uk: (BOB member since 2005-01-06)

12

This reference (as well as the one about udf and prompt) appears to relate to scalar functions rather than table valued functions. Please forgive me if I am explaining something that is already known by the group, but I think that it is important for those reviewing this thread to understand that (at least from my perspective) the true goal is to return a data set that walks, talks and acts like a table given one or more values that are passed to it in a parameter. This “table” would be joined to other tables in the universe and treated just like every other table.

Scalar functions return one value and are generally discouraged from a performance perspective. Table valued functions however are very powerful and performant. They are essentially parameterized views.

The only way I have found to include a table valued function in a universe is to “wrap” it in a derived table in the universe or in a view in the database as suggested in my inital post. Does anybody have any suggestions that would eliminate this unecessary overhead?

thx,
mjk

mkrigba :us: (BOB member since 2006-11-22)

13

Sorry Mike,

I was answering other questions on this thread, rather than yours :).

I’m well aware that UDFs are not perfomant, although I have used them to calculate holidays between two date columns.

I’m unsure how to answer your requirement, apart from what you have already suggested.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)