SQL Server - I'm calling a Stored Procedure in a LR and wanting to display the returned dataset after the LR executes. The SP creates a table variable (@tmpTable), inserts data into @tmpTable, and then returns the @tmpTable back to the LR. Simple enough.
My issue is that the LR is only displaying a single column titled, "numrowchanged", with a value of 7. I'm assuming the 7 is the number of records I inserted into @tmpTable.
The SP looks like this:
CREATE procedure [livelink].[processData](@DataID int)
AS
DECLARE @tmpTable TABLE
(
GroupID varchar(max),
GroupName varchar(max),
GroupType varchar(max),
UserName varchar(max),
Processed BIT
)
BEGIN
< insert data into @tmpTable here>
--run the select statement and that's it
SELECT * FROM @tmpTable
RETURN
END
******
You might be wondering why I don't just use a table-valued UDF, it's because I was going to manipulate the data in @tmpTable after I had populated it. I don't think I can do any updates in a UDF. So, I'm stuck with a SP.
Another thought I had was to execute the sp on the first line of the LR and store that value in a temp table instead of a temp variable (#tmpTable). Then, the second line would by my "select * from #tmpTable", but I don't think you can execute multiple values within a LR.
Any ideas would be helpful.
Thanks.
