EXEC & sp_executesql

Question

Post reply

EXEC & sp_executesql

Mithrandir

SSCrazy

Points: 2628

October 12, 2004 at 12:15 pm

#163441

Hi guys. I'm tired of this thing

DECLARE @mes NVARCHAR(2)
SET @mes = CASE WHEN Day(GETDATE()) < 22 THEN Convert(NVARCHAR, Month(GETDATE())) ELSE Convert(NVARCHAR, Month(DateAdd(Month, 1, GETDATE()))) END

sp_executesql N'SELECT R.clave, R.tamano
, AVG(Cli.dias_stock_min_ppg) AS dias_stock_min_ppg
, AVG(Cli.dias_stock_min_sat) AS dias_stock_min_sat
, CASE SUM(EC.est' + @mes + N') WHEN 0 THEN 0 ELSE((R.inv_sjd*SUM(EC.porcentaje_de_consumo)/100) * AVG(DM.dias_lab_sat' + @mes + N')) / SUM(EC.est' + @mes + N') END AS cob_sjd_vs_est
, CASE SUM(EC.est' + @mes + N') WHEN 0 THEN 0 ELSE((SUM(R.bodega) + SUM(R.inv_linea_clte) + SUM(R.inv_clte_cto_mezclas)) * AVG(DM.dias_lab_sat' + @mes + N')) / SUM(EC.est' + @mes + N') END AS sat_cob_vs_est
, R.tc_promedio
, SUM(EC.porcentaje_de_consumo) AS porcentaje_de_consumo
FROM dbo.tblReporte R
INNER JOIN dbo.tblEstimadosClientes EC
ON R.clave = EC.clave AND R.tamano = EC.tamano AND R.id_cliente = EC.id_cliente
INNER JOIN dbo.tblClientes Cli
ON R.id_cliente = Cli.id_cliente
INNER JOIN dbo.tblDatosMensuales DM
ON R.id_cliente = DM.id_cliente
GROUP BY R.clave, R.tamano, R.inv_sjd, R.tc_promedio'

Someone of you know why this same instruction is executed by EXEC(), but not by sp_executesql?

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

The only thing I know of that I read somewhere, can't remember where, was that sp_executesql

can take advantage of query plans where exec cannot.

Someone correct me if I'm wrong!

Kindest Regards,

David Burrows SSC Guru Points: 65142 More actions · Answer 2

As written you must use

EXEC sp_executesql

or do you mean that the sql works with EXEC but not sp_executesql ?

Trigger, yes sp_executesql will cache query plans but if I remember right you have to use fully qualified names for it to do so

Far away is close at hand in the images of elsewhere.
Anon.

Mithrandir SSCrazy Points: 2628 More actions · Answer 3

Yes, you're right. I had forgotten the EXEC prior to sp_executesql ¬¬

Final code is like this:

DECLARE @mes NVARCHAR(2)

DECLARE @sql NVARCHAR(1000)

SET @mes = CASE WHEN Day(GETDATE()) < 22 THEN Convert(NVARCHAR, Month(GETDATE())) ELSE Convert(NVARCHAR, Month(DateAdd(Month, 1, GETDATE()))) END
TRUNCATE TABLE dbo.tblCoberturaTc

SET @sql = N'SELECT R.clave, R.tamano
  , AVG(Cli.dias_stock_min_ppg) AS dias_stock_min_ppg
  , AVG(Cli.dias_stock_min_sat) AS dias_stock_min_sat
  , CASE SUM(EC.est' + @mes + N') WHEN 0 THEN 0 ELSE((R.inv_sjd*SUM(EC.porcentaje_de_consumo)/100) * AVG(DM.dias_lab_sat' + @mes + N')) / SUM(EC.est' + @mes + N') END AS cob_sjd_vs_est
  , CASE SUM(EC.est' + @mes + N') WHEN 0 THEN 0 ELSE((SUM(R.bodega) + SUM(R.inv_linea_clte) + SUM(R.inv_clte_cto_mezclas)) * AVG(DM.dias_lab_sat' + @mes + N')) / SUM(EC.est' + @mes + N') END AS sat_cob_vs_est
  , R.tc_promedio
  , SUM(EC.porcentaje_de_consumo) AS porcentaje_de_consumo
 FROM dbo.tblReporte R
  INNER JOIN dbo.tblEstimadosClientes EC
   ON R.clave = EC.clave AND R.tamano = EC.tamano AND R.id_cliente = EC.id_cliente
  INNER JOIN dbo.tblClientes Cli
   ON R.id_cliente = Cli.id_cliente
  INNER JOIN dbo.tblDatosMensuales DM
   ON R.id_cliente = DM.id_cliente
 GROUP BY R.clave, R.tamano, R.inv_sjd, R.tc_promedio'
EXEC sp_executesql @sql

Thanks

TriggerMe SSChampion Points: 11752 More actions · Answer 4

David Burrows,

You are correct, you have to use fully qualified names. I forgot to mention that.

Cheers.

Kindest Regards,

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

If you're saying that this will work if you change the last line to EXEC (@sql), then why not do that? I don't know why sp_executesql can't handle it, but I would go ahead and use EXEC.

The issue of sp_executesql being more efficient because of execution plan reuse doesn't apply to this query (in my opinion) because you aren't just making parameter substitutions, you're changing field names used in the aggregate functions. (As mentioned above, you also need to use fully qualified object names to expect execution plan reuse.)

I worry about execution plan reuse more for short queries where compilation is a significant part of the execution time, but with this kind of reporting query (on a large amount of data) the compile time is a tiny fraction of the total execution time.

If there's no advantage to sp_executesql in this case, the only reason to continue to play with this is the intellectual challenge of figuring out why it doesn't work.

David Burrows SSC Guru Points: 65142 More actions · Answer 6

I agree with you Scott, plan reuse in this scenario is probably minimal, I just answered the questions stated without commenting on the usage.

Again, not in this case, but sql will, if it can, convert non parameterised queries into parameterised queries to reuse the plan.

Far away is close at hand in the images of elsewhere.
Anon.