Max Row Limitation in Stored Procedure

Get Help
1

Hi!

We have some questions that access a stored procedure.
When we call the execution inside of Metabase, the return is incorrect.
Inside of the procedure, we compute around 15k lines and make some calculations to return around 30 rows only.
Executing the same procedure in sql management, works correctly,
There’s something to fix this?

2

@AdrianoSM could you please share your troubleshooting info, version of the SQL database you are using and the syntax you are executing? Thanks

3

Hello @Luiggi

Our main database is SQL Server: Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 7 2020 08:46:15 Copyright (C) 2019 Microsoft Corporation

Create or alter procedure dbo.Sp_Report_analise_ruptura_reincidente @id_plataforma int

as begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

drop table if exists #Cluster_pedidos;
drop table if exists #cluster_pedidos_sm;
drop table if exists #cluster_pedidos_ifo;
drop table if exists #TmpDados_1_sm;
drop table if exists #dados_1_ifo;


select a.dt_pedido, a.plataforma, a.id_loja, b.id_pedido, b.id_loja_produto, b.dt_cadastro, b.dt_separacao, b.qtd_estoque_emissao, b.qtd_estoque_picking
  into #TmpCluster_pedidos
  from [producao].sm_v1.dbo.pedido a (Nolock)
  join [producao].sm_v1.dbo.pedido_item b (Nolock)
	on a.id_pedido = b.id_pedido
 where month(a.dt_pedido) = month(getdate())
			 and year(a.dt_pedido) = year(getdate())

   and b.indisponivel = 1
   and a.plataforma = @id_plataforma
 order by a.plataforma, a.dt_pedido, a.id_loja, b.id_pedido, b.id_loja_produto, b.dt_cadastro, b.dt_separacao;


if @id_plataforma = 1
Begin 
	select * 
	into #TmpDados_1_sm
	from (
	select '1 - sm' analise_plataforma, a.*, 
			b.dt_pedido dt_pedido2, b.id_loja id_loja_2, b.id_pedido id_pedido_2, b.id_loja_produto id_loja_produto_2, b.dt_cadastro dt_cadastro_2, b.dt_separacao dt_separacao_2,
			b.qtd_estoque_emissao qtd_estoque_emissao_2, b.qtd_estoque_picking qtd_estoque_picking_2,
			row_number() over(partition by a.id_pedido, a.id_loja_produto order by b.dt_pedido) ordem 
	  from #cluster_pedidos_sm a
	  left join #cluster_pedidos_sm b
		on a.id_loja_produto = b.id_loja_produto
	   and a.id_pedido <> b.id_pedido
	   and a.dt_pedido < b.dt_pedido
	   and a.id_loja = b.id_loja
	   and cast(a.dt_pedido as date) = cast(b.dt_pedido as date)
	 where b.dt_cadastro is not null
	   and b.qtd_estoque_emissao <= a.qtd_estoque_emissao
	   ) T
	   where T.ordem = 1
	order by id_loja, dt_pedido, id_pedido, id_loja_produto;


	select '1 - sm' analise_plataforma, datepart(day, a.dt_pedido)  data_analise, count(*) total
	  from #TmpDados_1_sm a
	group by datepart(day, a.dt_pedido) 
	order by 2;

End

{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.8+10",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.8",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.8+10",
"os.name": "Linux",
"os.version": "4.15.0-112-generic",
"user.language": "en",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"googleanalytics",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.1"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.8"
}
},
"run-mode": "prod",
"version": {
"date": "2020-08-04",
"tag": "v0.36.3",
"branch": "release-0.36.x",
"hash": "a792f14"
},
"settings": {
"report-timezone": "America/Sao_Paulo"
}
}
}

4

@AdrianoSM You are likely seeing this issue - you can check the database query log to see if that is the problem compared other programs:
https://github.com/metabase/metabase/issues/9940 - upvote by clicking :+1: on the first post