Jump to content
Rodcst

Vba receber linha de resultado de uma store procedure SQL com um parametro

Recommended Posts

Rodcst

Bom dia,

Tenho uma store procedure em SQL que recebe um argumento ex: 0001 e devolve apenas uma linha com 6 colunas ex: cliente, plafon, ccc, clr, ccd,encp.

Alguém me pode indicar um exemplo vba que chame a store procedure com o argumento ex: cliente e receba a linha com as n colunas?

Já encontrei vários exemplos na net mas nenhum trata a recepção de uma linha como resultado.

Desde já agradeço a vossa preciosa ajuda.

Edited by Rodcst

Share this post


Link to post
Share on other sites
Rodcst

Solução:

Dim Conn As ADODB.Connection

Dim ADODBCmd As ADODB.Command

Dim Rs As ADODB.Recordset

Dim i As Integer

Dim sConnect As String

'Resultados

Dim strCliente As String

Dim Adiantamentos As Double

Dim ContaCorrente As Double

Dim ContaChequesDatados As Double

Dim ContaLetras As Double

Dim ContaContencioso As Double

Dim GuiasPorFaturar As Double

Dim EncomendasPendentes As Double

Dim Totalde2a8 As Double

Dim Plafon As Double

strCliente = Cliente

'--CONNECTION STRING---------------------------------------------------------------------

sConnect = "driver={sql server}; server=.\instancuia; Database=BD; UID=SA; PWD=sa;"

'----------------------------------------------------------------------------------------

'Faz ligação

Set Conn = New ADODB.Connection

Conn.ConnectionString = sConnect

Conn.Open

'Corre Store Procedure

Set ADODBCmd = New ADODB.Command

ADODBCmd.ActiveConnection = Conn

ADODBCmd.CommandText = "exec [sp_VIMEstadoFinanceiro] @Cliente = N'" & strCliente & "'"

Set Rs = ADODBCmd.Execute()

'Carrega vairáveis com resultados

Rs.MoveFirst

strCliente = Rs.Fields(0).Value

Adiantamentos = Rs.Fields(1).Value

ContaCorrente = Rs.Fields(2).Value

ContaChequesDatados = Rs.Fields(3).Value

ContaLetras = Rs.Fields(4).Value

ContaContencioso = Rs.Fields(5).Value

GuiasPorFaturar = Rs.Fields(6).Value

EncomendasPendentes = Rs.Fields(7).Value

'Limpa rs

Rs.Close

Set Rs = Nothing

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.