reportviewer dynamic stored procedure

Question

User152990470 posted

Good day.  I have the following problem. In my asp.net 4.0 web system there is a stored procedure that puts together a sql string based on a users privilages (stored in a session variable session("priv") ) and then executes this string to return results.

 

set @iwherestring = (SELECT dbo.filterstring('o.depot', 'Depot' ,@priv))
SET @SQLString =  N'select o.orderno,i.itemNo,o.clientCode, o.orderdate, o.depot , i.product, i.amount from orders o join order_items i on o.orderno = i.orderno and o.clientCode = i.clientCode ' + @iwherestring;

EXECUTE sp_executesql @SQLString

The procedure works perfectly when called from withing SSMS but when i try to attach the stored procedure to the datasource of a reportviewer control it does not work. Is this becaue i am putting the sql together dynamically?

Any help would be greatly appreciated!

Answer 1

User-67082742 posted

If you are taking query type as Stored Proc and associating with stored proc, it might be a problem.

Try using it in query statement itself  with the execute statement. Or is this what you are doing already?

Answer 2

User152990470 posted
Hi thanks for the reply, the query works when i call the stored procedure from within the ssms query window. When i associate the report viewer contol with the stored procedure that fails...

Answer 3

User-67082742 posted

yep... So please try doing the sam ein SSRS dataset query window. I mean add the stored proc with exec statement there and then it should work like it works in SSIS query window for you.

Umm.. I think i am gettng it wrong. Do you mean you are associating stored proc from .NetCode to reportViewer control?