HELP - MSSQL 6.5 Stored Proc Error

Tony Lorenzo

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

HELP - MSSQL 6.5 Stored Proc Error

Hello,

We are building a C/S D4 app with MSSQL 6.5, BDE 5.1, ODBC DataSource
instead of a BDE Alias.

I have a stored Proc on the Server that is returning an output param of
Float - a summarization of a column. When it returns NULL in the event that
there is no matching data, a EDBEngineError gets raised. Other things to
note is that the SP component is persistent but I am changing the SP name,
preparing it, executing, in cycles of different SPs.

Message: 1: General SQL error., Category: 51, Error code: 13059
Subcode: 3, Native error: 0

Message: 2: [Microsoft][ODBC SQL Server Driver]Indicator variable required
but not supplied
Category: 51, Error code: 13059, Subcode: 3, Native error: 0

An example of the Stored Procedure;

CREATE PROCEDURE GET_SUM
@P_ACODE INTEGER = NULL,
@P_TOT_PCT FLOAT = NULL OUTPUT

AS

BEGIN

SELECT
@P_TOT_PCT = SUM( M.PERCENT )
FROM
MYTABLE M
WHERE
M.ACODE = @P_ACODE
END

My many thanks ahead of time,

Anthony Lorenzo
Programmer Analyst
BNY ESI & Co.
alore...@bnyesi.com

Jamal Kubee

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:HELP - MSSQL 6.5 Stored Proc Error

hi,

why don't you use IsNull function and set the output to zero
like

SELECT @P_TOT_PCT =IsNull(SUM( M.PERCENT ),0)
FROM MYTABLE M WHERE M.ACODE = @P_ACODE

then this will return 0 if the value is null

Quote

Tony Lorenzo wrote in message <7aa4oo$55...@forums.borland.com>...
>Hello,

>We are building a C/S D4 app with MSSQL 6.5, BDE 5.1, ODBC DataSource
>instead of a BDE Alias.

>I have a stored Proc on the Server that is returning an output param of
>Float - a summarization of a column. When it returns NULL in the event
that
>there is no matching data, a EDBEngineError gets raised. Other things to
>note is that the SP component is persistent but I am changing the SP name,
>preparing it, executing, in cycles of different SPs.

> Message: 1: General SQL error., Category: 51, Error code: 13059
> Subcode: 3, Native error: 0

> Message: 2: [Microsoft][ODBC SQL Server Driver]Indicator variable
required
>but not supplied
> Category: 51, Error code: 13059, Subcode: 3, Native error: 0

>An example of the Stored Procedure;

>CREATE PROCEDURE GET_SUM
> @P_ACODE INTEGER = NULL,
> @P_TOT_PCT FLOAT = NULL OUTPUT

>AS

>BEGIN

> SELECT
> @P_TOT_PCT = SUM( M.PERCENT )
> FROM
> MYTABLE M
> WHERE
> M.ACODE = @P_ACODE
>END

>My many thanks ahead of time,

>Anthony Lorenzo
>Programmer Analyst
>BNY ESI & Co.
>alore...@bnyesi.com

Ian Broadfoo

Delphi Developer

Wed, 18 Jun 1902 08:00:00 GMT

Re:HELP - MSSQL 6.5 Stored Proc Error

Tony,

Do you have the stored procedures relative paramater set to 'Output' and the
check box ticked to allow nulls as return values.

Quote

Tony Lorenzo wrote in message <7aa4oo$55...@forums.borland.com>...
>Hello,

>We are building a C/S D4 app with MSSQL 6.5, BDE 5.1, ODBC DataSource
>instead of a BDE Alias.

>I have a stored Proc on the Server that is returning an output param of
>Float - a summarization of a column. When it returns NULL in the event
that
>there is no matching data, a EDBEngineError gets raised. Other things to
>note is that the SP component is persistent but I am changing the SP name,
>preparing it, executing, in cycles of different SPs.

> Message: 1: General SQL error., Category: 51, Error code: 13059
> Subcode: 3, Native error: 0

> Message: 2: [Microsoft][ODBC SQL Server Driver]Indicator variable
required
>but not supplied
> Category: 51, Error code: 13059, Subcode: 3, Native error: 0

>An example of the Stored Procedure;

>CREATE PROCEDURE GET_SUM
> @P_ACODE INTEGER = NULL,
> @P_TOT_PCT FLOAT = NULL OUTPUT

>AS

>BEGIN

> SELECT
> @P_TOT_PCT = SUM( M.PERCENT )
> FROM
> MYTABLE M
> WHERE
> M.ACODE = @P_ACODE
>END

>My many thanks ahead of time,

>Anthony Lorenzo
>Programmer Analyst
>BNY ESI & Co.
>alore...@bnyesi.com

Other Threads

1. MSSQL 7.0 Stored Proc's Error

2. Stored Proc fine in IB, error in MSSQL

3. SQL 6.5 MDAC 2.5 Stored Proc problem

4. Can't execute Stored Proc (D4,SQL 6.5 sp5a )

5. MS SQL 6.5 Stored Proc with Text (Memo) Param

6. Getting Output Parameters from SQL Server 6.5 Stored Proc

7. HELP! D1/MSSQL 6.5 General SQL Error

8. MSSQL 6.5 Stored Procedure functions diferent

9. Stored Procedures,ODBCExpress and MSSQl 6.5

10. Can't find Stored Procedures when moving from MSSQL 6.5 to 7