How to write SQL stored procedures for Qlik

Recently I wanted to add one of my stored procedures for a different report into a dashboard to show the power of Sense (integrating pre system data with the new ERP system, Awesome!)

However, whatever I did, my stored procedure wouldn't run in Qlik Sense... so I did what I usually do in this situation and ran back to Qlikview to see if I could identify the problem and I couldn't! I should point out that this code was live and running fine in both SQL and crystal reports (a necessary evil for other projects!)

TL;DR version: the SP layout is at the end of the article!



This led to me pulling apart my code over and over and over again... I had just resolved myself to taking the query from the procedure and putting it straight in Sense which worked fine but meant that any fixes in the future would need to be done in two places - not ideal.

But I perservered! At the beginning of my procedure I was inserting details into a logging table that I use for stats, removing this fixed my issue... what was going on!

I figured it out, that first insert was throwing off the rest of the query - Sense was getting a response before the data was ready. Deleting everything but the final select statement worked, not ideal but we have data!

Following this the fix is pretty simple, I just needed to set nocount on! but again this failed... It seemed like this problem was never going away, but it worked a minute ago, I needed to reactivate the row counts just before returning the results...

Here's my future template for making things Qlik happy.

Create Proc [schema].[ProcName]
 (@ParametersHere int)
 as
 Begin
Set nocount On
--This is where you can run extracts, inserts, etl, etc


Set nocount Off
--turn off nocount just before you return your results
Select *
from Results
 End