How to Insert and select in Stored procedure in SQL through foxpro9

[Niki_S]

Can someone please help me to create an SP in sql server.
I need to insert data through foxpro9 into my SP. And I need to auto increment a number field according to a group. As an example,
Table1

 ParaId    ParaNo    ParaYear     ParaCD
  218       1         2020         vtmc
  217       1         2020         vtmf
  456       1         2021         sfcc

If that is my table in my SP and I need to insert data from my foxpro table. In my foxpro table there have duplicate records like this.

Table2

    ParaID    ParaNo     ParaYear     ParaCD    FctCD    INVnO
    218       1          2020         vtmc      vtm      001/20/S
    218       1          2020         vtmc      vtm      001/20/S
    218       1          2020         vtmc      vtm      001/20/S

Now I need to insert one record set to my SP through foxpro and I need my output as below,

SP,

 ParaId    ParaNo    ParaYear     ParaCD
  218       1         2020         vtmc
  [b]218       2         2020         vtmc[/b][COLOR=#CC0000][/color]
  217       1         2020         vtmf
  456       1         2021         sfcc

How can I crate an SP for these and how to pass my data from foxpro table to SP

 

[Chris Miller]

Where does ParaNo 2 come from? Your data is still not consistent.

A simple method to call a stored proc is to execute the MSSQL code for it from SQLEXEC. So the core line of code is

lnConnectionhandle = SQLSTRINGCONNECT(Connectionstring)
If lnConnectionhandle>0
   [highlight #FCE94F][b]SqlExec(lnConnectionhandle,"storedprocname(?para1,?para2,...)")[/b][/highlight]
   SqlDiconnect(lnConnectionhandle)
Else
   If AERROR(laError)>0
      ? laError[1,1], laError[1,2], laError[1,3],...
   Endif
Endif

I think it will be hard enough as a starting point to make the connection work, but maybe you're already inheriting that code and can concentrate on the SQLEXEC part, mainly. And it's as simple as that, SQLEXEC forwards T-SQL (not just INSERT, SELECT, UPDATE, DELETE, all T-SQL, also scripts or storedproc calls) to the clientside ODBC driver which forwards it to the connected SQL Server instance and that returns a result (if it's not void) at least an error state.

Chriss