Pass a table to a SQL Server stored procedure

[pstiglich]

Hello,

      I'd like to pass an entire Qlik table to a SQL Server stored procedure using a table parameter, rather than looping through the Qlik table rows and calling the SP once per row.  In SQL Server I can define a type (CREATE TYPE dbo.xyz as table ...)  and load data into the type, declare a variable as that type, and then pass that variable to a stored procedure.  

     Can I declare a Qlik variable as that table type and then pass in the Qlik table to that variable and then send it into the stored procedure call? 

Thanks,

Pete

[HenrikStaunPoulsen]

I know this is an old post, with no replies.
But I have asked the same question on Stackoverflow.com:
https://stackoverflow.com/questions/75415679/passing-a-tvp-parameter-to-a-stored-procedure-from-qlik as I've been searching high and low for an answer.
Can anyone help, please?

[marcus_sommer]

I think with the classical load it won't be possible because Qlik doesn't execute any SQL else just transfers the statement per driver to the database and received on this way back the results and AFAIK the access/connection to the internal Qlik tables isn't possible on this way.

Possible is to transfer variable-values and a variable could contain more as a single value else multiple combined ones which may also structured in some way like tables or xml/json structures respectively as nested arrays. So transferring the information should be possible but would need some adjusted handling on the database-side.

Easier as such approach might be just to store the table as csv and then loading them within the database - this might be triggered from an appropriate sql-call.

[HenrikStaunPoulsen]

hi Marcus,

Thank you very much for trying to come up with a solution for me.

It is true that a JSON parameter could do the trick, but it is not nearly as elegant as a C# DataTable, nor is it particularly fast.
I have had good results with TVP, except if I needed to change the structure (as in adding columns). There is no ALTER TYPE command, and you have to drop all references to the datatype before you can drop and re-create that.

So I hope that there are Qlik programmers out there, that will not accept that C# works better than Qlik.
I'm just a SQL database guy, so I hope that you forgive my silly questions.

Best regards
Henrik