0

I am following this guide to create a heatmap.

http://sqljason.com/2012/03/heat-maps-for-ssrs-using-map-control.html

One of the steps is to feed a stored procedure another stored procedure that generated my data.

Mine looks something like this:

exec dbo.CreateHeatMap 20, 25, 'exec ZZ_N_FinalEcomDashboardCustomerRFMUnPivot 03, 001, 1/1/2014, 1/1/2020'

The problem is those dates. At first I was wrapping them in apostrophes like '1/1/2014' but that seemed to break the whole line.

I've tried every combination of apostrophes, declaring and setting variables, using concatenate or plus signs, and everything else but I can't seem to get it to work.

I am building this line in a SSRS report with parameters. So my ultmate goal is something like this

exec dbo.CreateHeatMap 20, 25, 'exec ZZ_N_FinalEcomDashboardCustomerRFMUnPivot @Company, @Division, @Date, @date2'

with those being parameters in my report. And I am using this line to create a dataset in my report.

What is the correct syntax for this?

nathan
  • 83
  • 7
  • The *correct* way to escape single quotes is to double them - what happens when you do that? – Dale K Feb 27 '20 at 22:44
  • 1
    This seems dangerously open to SQL injection. I wonder what would happen if someone ran `exec dbo.CreateHeatMap 20, 25, 'DROP PROC dbo.CreateHeatMap;';` – Thom A Feb 27 '20 at 22:46
  • @DaleK double quotes doesnt work either because I think then I am feeding a string like '1/1/2014' rather than a datetime variable. But would you know how to skip all the quotes and just feed it my current report parameters directly? – nathan Feb 27 '20 at 22:49

1 Answers1

0

It's probably this:

exec dbo.CreateHeatMap 20, 25, 'exec ZZ_N_FinalEcomDashboardCustomerRFMUnPivot ''03'', ''001'', ''1/1/2014'', ''1/1/2020'''

The leading zero "numbers" will need to be quoted as strings too if it's mandatory they retain their leading zero

Can't help but think there is a deeper flaw here.. but without looking at CreateHeatMap code I can't say for sure. Passing an sql to run to a process seems like a recipe for a hacking disaster though

Caius Jard
  • 72,509
  • 5
  • 49
  • 80