execute stored procedure in a datasource

[cbrydon]

Hi,

I am wondering if it possible to execute a stored procedure as part of a datasource.
I have a very basic report connecting to SQL Server and a datasource with the following sample SQL code.

DECLARE @MyTable TABLE
(
ProductID int UNIQUE,
Price money CHECK(Price < 10.0)
)
Select * from @MyTable

It works when I click on Retrieve Columns - both the ProductID and Price columns appear. However,
the SQL code I actually need is more complex in that I need it to union a couple of columns from
multiple tables and the list of tables could change. To test whether this would be possible, I first tried
modifying the SQL code as shown below, but when I try to Retrieve Columns, none appear. Is there a
way to do accomplish what I am trying? I am using Stimulsoft Reports.Web 2017.1.6 from June 3, 2017.
Hope you can help.

DECLARE @MyTable TABLE
(
ProductID int UNIQUE,
Price money CHECK(Price < 10.0)
)
Set @DynSQL = 'Select * from @MyTable'
execute sys.sp_executesql @DynSQL

[Edward]

Hi Carl,

Dynamic SQL queries are very powerful indeed, especially when they are used together with parameters, but, on the other hand, it is very hard to optimize and to catch the exceptions if any of them appear later on, especially when there are quite complex SQL with lots of temporary tables of big size.

Do you mind if I would suggest to optimise the Dynamic SQL with a very basic approach that has been explained here:

https://blog.sqlauthority.com/2016/06/0 ... -week-074/

Obviously you could use tables as parameters in the union statement:

select MyColumn from @MyTable1
union
select MyColumn2 from @MyTable2

Alternatively, please create a separate stored procedure that preferably does not have temporary tables, but table variables instead. And that approach should hopefully work fine.
Please let us know how it goes.

Thank you,
Edward

[cbrydon]

Thanks Edward, I'll give this a try this weekend and see what I can come up with.

[Edward]

Hi Carl,

No problem, let us know how it goes.

Have a great weekend!
Edward

[cbrydon]

Hi Edward,

Below is a sql script that does exactly what I want - in SQL Server Management Studio. Unfortunately I can't get it to run
as the script for a datasource in a report. I think, as you suggested, that I'll have to create a stored procedure or maybe
a function that does this and then I can query the function instead.

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''
DECLARE cursor1 CURSOR FOR
select name
from sys.views
where name like 'FType%'
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Add the select code.
Set @DynSQL = @DynSQL + 'Select ID_A,ID_B from reports.' + @TableName
FETCH NEXT FROM cursor1
INTO @TableName
-- If the loop continues, add the UNION ALL statement.
If @@FETCH_STATUS = 0
BEGIN
Set @DynSQL = @DynSQL + ' UNION ALL '
END
END
CLOSE cursor1
DEALLOCATE cursor1
EXEC(@DynSQL)

[Edward]

Hi Carl,

Yes, you are correct the sql script is quite complex and probably is best to put it to the stored procedure.

In general, if there are any temp tables/table variables are used in the SQL Datasource, please use the trick with the following code in the stored procedure, that will return only the correct metadata of the query when it is required and the correct data when the data is needed:

Code: Select all

IF 1=0 BEGIN
SET FMTONLY OFF
END

as per the following discussion: viewtopic.php?&t=3137

Thank you,
Edward

[cbrydon]

Hi Edward, A colleague of mine recently discovered your "trick" in this post and let me know know about it. Sorry, I must have missed it back in January.
I wanted to let you know that it does work - I appreciate the help!

Carl

[Edward]

Hi Carl,

Awesome news! Thank you for letting me know it worked!

Please let us know if you need any help. Have a great weekend!

Thank you,
Edward