FoxPro Help and Support - how to call sql server 2000 stored procedure from foxpro

Author

how to call sql server 2000 stored procedure from foxpro

Sunny

2005-04-07, 8:06 pm

Hi all,

It seems to be easy but I couldnt find the way. Here is my stored procedure
at server:

CREATE PROCEDURE GetTotalSales
@SaleAmt money = 0 OUTPUT,
@ClientId char(10),
@ToDate varchar(10)

AS
Begin
.....
--Some code
Set @SaleAmt = @TotalSales --Found from above code
END
GO

Works fine when run in query analyzer.

Trying to call in VFP 8.0 with following code
nBal = 0
?SQLEXEC(gnHandle,"EXEC GetBal
000002542','12/31/2004'") --gnHandle is tested and value is positive

This gives me error.

Can anyone please help me to correct the syntax.

Thanks.

John Veazey

2005-04-07, 8:06 pm

Sunny,

There are two ways you can handle output parameters through FoxPro.

This first example uses the output command to, in effect, pass the @SaleAmt
by reference into the stored procedure. Here's the example SQL statements.

declare @SaleAmt money
exec gettotalsales @SaleAmt output,'myclientid',
'mydate'
select @SaleAmt

Since we know this works and we know that a cursor is returned back to
FoxPro, you can copy it directly into FoxPro like this. This is probably the
simplest answer.

?SQLEXEC(gnHandle,"declare @SaleAmt money " +;
"exec gettotalsales @SaleAmt output,'myclientid',
'mydate' " +;
"select @SaleAmt")

--

The other way to handle it is to pass a variable from FoxPro directly into
SQL using an alternate form of SQL Passthrough. It takes advantage of
several ideas which I find not very well documented.

nSaleAmt = 0
?SQLEXEC(gnHandle,"{exec gettotalsales(?@nSaleAmt, 'myclientid', 'mydate'}))
?nSaleAmt

Notice that I'm using "?" and "@" to reference the VFP variable within the
SQL Statement. The use of "?" allows you to pass in a variable into a SQL
statement, which is interpreted into the correct ODBC data type and is used
to execute a prepared statement (which is all handled by the backend).

The use of "@" allows you to pass in the variable by reference, just like
when passing a variable by reference into a procedure. Make sure that you
initialize the variable before you pass it into the SQL Statement, otherwise
the ODBC drivers will generate an error because of a type conflict.

--
John L Veazey
Platypus & Wombat Lead Developer
Web: http://platypus.tucows.com

"Sunny" wrote:

> Hi all,
>
> It seems to be easy but I couldnt find the way. Here is my stored procedure
> at server:
>
> CREATE PROCEDURE GetTotalSales
> @SaleAmt money = 0 OUTPUT,
> @ClientId char(10),
> @ToDate varchar(10)
>
> AS
> Begin
> .....
> --Some code
> Set @SaleAmt = @TotalSales --Found from above code
> END
> GO
>
> Works fine when run in query analyzer.
>
> Trying to call in VFP 8.0 with following code
> nBal = 0
> ?SQLEXEC(gnHandle,"EXEC GetBal
> 000002542','12/31/2004'") --gnHandle is tested and value is positive
>
> This gives me error.
>
> Can anyone please help me to correct the syntax.
>
> Thanks.
>
>
>

Phil H

2005-05-02, 11:24 am

John, this syntax didn't work. You're missing closing quotes in the SQLEXEC.

We tried
nourvar1 = 0
nourvar2 = 0

=SQLEXEC(gnHandle,"{exec OurSP(?@nourvar1, ?@nourvar2)}")
? nourvar2

and got no results. (@nOurvar2 is the output variable of the SP.)

Any other ideas?

"John Veazey" wrote:
[color=darkred]
> Sunny,
>
> There are two ways you can handle output parameters through FoxPro.
>
> This first example uses the output command to, in effect, pass the @SaleAmt
> by reference into the stored procedure. Here's the example SQL statements.
>
> declare @SaleAmt money
> exec gettotalsales @SaleAmt output,'myclientid',
'mydate'
> select @SaleAmt
>
> Since we know this works and we know that a cursor is returned back to
> FoxPro, you can copy it directly into FoxPro like this. This is probably the
> simplest answer.
>
> ?SQLEXEC(gnHandle,"declare @SaleAmt money " +;
> "exec gettotalsales @SaleAmt output,'myclientid',
'mydate' " +;
> "select @SaleAmt")
>
> --
>
> The other way to handle it is to pass a variable from FoxPro directly into
> SQL using an alternate form of SQL Passthrough. It takes advantage of
> several ideas which I find not very well documented.
>
> nSaleAmt = 0
> ?SQLEXEC(gnHandle,"{exec gettotalsales(?@nSaleAmt, 'myclientid', 'mydate'}))
> ?nSaleAmt
>
>
> Notice that I'm using "?" and "@" to reference the VFP variable within the
> SQL Statement. The use of "?" allows you to pass in a variable into a SQL
> statement, which is interpreted into the correct ODBC data type and is used
> to execute a prepared statement (which is all handled by the backend).
>
> The use of "@" allows you to pass in the variable by reference, just like
> when passing a variable by reference into a procedure. Make sure that you
> initialize the variable before you pass it into the SQL Statement, otherwise
> the ODBC drivers will generate an error because of a type conflict.
>
> --
> John L Veazey
> Platypus & Wombat Lead Developer
> Web: http://platypus.tucows.com
>
> "Sunny" wrote:
>

Fred Taylor

2005-05-02, 8:25 pm

FWIW, I've never been able to make that syntax work for return values.

Try this instead:

nourvar1 = 0
nourvar2 = 0

=SQLEXEC(gnHandle,"exec OurSP ?@nourvar1, ?@nourvar2")

--
Fred
Microsoft Visual FoxPro MVP

"Phil H" <philhege@newsgroups.nospam> wrote in message
news:3DE699F6-9199-4BEA-B941- FAC131A83CCD@microso
ft.com...[color=darkred]
> John, this syntax didn't work. You're missing closing quotes in the
> SQLEXEC.
>
> We tried
> nourvar1 = 0
> nourvar2 = 0
>
> =SQLEXEC(gnHandle,"{exec OurSP(?@nourvar1, ?@nourvar2)}")
> ? nourvar2
>
> and got no results. (@nOurvar2 is the output variable of the SP.)
>
> Any other ideas?
>
>
> "John Veazey" wrote:
>

Sponsored Links