Stored Proc via Adv SQL widget returns no data

Stored Proc via Adv SQL widget returns no data

  

I've been working on this for too long, and I'm stumped.  We have a stored procedure that returns data when run with test parameters from SSMS (matching '' for VARCHAR, and null for otherwise).  But when we run the same stored procedure through a Advanced SQL widget, it returns nothing.  The call is formulated thusly:

/* %LogicalDatabase%=GetLogicalDatabase({EntityNameForTableInTargetDatabase})    */

EXEC Stored_Procedure @Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7, @Param8, @Param9, @Param10, @Param11, @Param12

The parameters are all session variables storing date, date time, integer, and varchar values.  Date/DateTime are passed as NULL if not present, integers are 0 if not set, and varchar are ''.

The stored proc has several steps, including truncating a table, creating four tables, adding indexes, inserting data into these tables (#temptables, not @variable tables) then pulling data together and kicking it out.  Like said, it returns the correct data if run from SSMS, but the OutSystems refuses.  We've checked database connections, database user privileges/rights, and everything's fun.  No errors are thrown - just no data.

Anything I have mis-set or mis-configured?  Anyone else seen this who has a solution?


Hi Chris,

Considering that db connection and security are good to go... what does that sp return, a result set or just one value? I suppose the output structure matches also what the sp returns?

Are you using Test from Service studio or actually running it within the application?

Nothing is returned whether I run it from Test in SS or from the application on the webpage.  The output structure is correct and matches the output from the stored page in columns and data types.


Solution

This can be ignored.  Our DBA solved the issue by forcing the '' values to be treated as null when they come through, and now things work.  I'm going to have her write all my SQL from now on, I think.

Thanks for looking at this, but it's been resolved.


Solution