Board index » delphi » Stored Procedure - Passing Argument in to a Stored Procedure

Stored Procedure - Passing Argument in to a Stored Procedure

Hi I have struggling with stored procedure problems with SQL 6.5 most of it
using stored procedure is very new to me.

I have tried it both ways and have different problems with each way. Note: I
want to do it run time is my end result.

I set up in my project in my datamodule the following when I set the active
properity true I get the error message - Error Creating cursor handle

See If you can follow what I did methord 1

Name - properity was set auto to StoredProc1

Step One - Drop Stored Procedure control into my datamodule which is unit2
in my project

Step Two - Set The Database Properity to MyDatabase

Step Three - ParambindMode default to pbByName

Step Four - StoredProcName was set by select in list of stored procedure the
name

  dbo.UnGenUniqueCallHistoryID;1

Step Five - Params using the edit two varables displayed Result, @TestUID
when selected on each one set the following:

 DataType ftInteger
 Name Result
 Paramtype ptResult

 DataType FtInteger
 Name @TestUID
 ParamType ptInput
 Value 0
 Type Integer

Step Six - Set the active properity to True

HERE IS WHERE I GOT THE ERROR MESSAGE -

This is methord 2 where I get a different error message - I even tried to
change the paramters around but still same error.
When I do it in code I get the Error message - general SQL Error

1       17:06:35  SQL Prepare: MSSQL -  :1 = dbo.UnGenUniqueCallHistoryID;1
:2,
2       17:06:35  SQL Misc: MSSQL - Set stored procedure on or off
3       17:06:35  SQL Data In: MSSQL - Param = 1, Name = Result, Type =
fldINT32, Precision = 0, Scale = 0, Data = NULL
4       17:06:35  SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
fldINT32, Precision = 0, Scale = 0, Data = 659
5       17:06:35  SQL Misc: MSSQL - Set statement type
6       17:06:35  SQL Execute: MSSQL -  :Result =
dbo.UnGenUniqueCallHistoryID;1 :InputInt,
7       17:06:35  SQL Error: MSSQL -
8       17:06:35  SQL Error: MSSQL - Unmapped SQL Error Code: 0
9       17:06:35  SQL Stmt: MSSQL - Reset

Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
Var
   SProc: TStoredProc;

Begin
   try
     SProc := TStoredProc.Create(Nil);
     SProc.DatabaseName := 'MyDataBase';
     SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
     // Reverese The order Parameters - Fix - Test Now - Still Fails
     SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
     SProc.Params.CreateParam(ftInteger, 'InputInt', ptInput);
     SProc.ParamByName('InputInt').AsInteger := MyValue;
     SProc.ExecProc;
     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
that was returned from Stored Procedure
     if Result > 0 Then
        Begin
           ShowMessage('This is the Old Call History Number Returned From
Server ' + IntToStr(Result));
           CallHistNo := Result;
        End
     Else
        Begin
           ShowMessage('The Call History Number Could Not Be Decreased' +
Char(13) +
            ' Because of another User Increased It On Server');
           // CallHistNo Stays the same as was - could Set Global Flag Here
           // Result will Equal -1 Self Induced - Error from Server

        End;

   finally
     SProc.Free;

   End;

End;
THIS IS THE OLD LOG HERE AND OLD FUNCTION

1       11:59:09  SQL Prepare: MSSQL -  :1 = dbo.UnGenUniqueCallHistoryID;1
:2,
2       11:59:09  SQL Misc: MSSQL - Set stored procedure on or off
3       11:59:09  SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
fldINT32, Precision = 0, Scale = 0, Data = 658
4       11:59:09  SQL Data In: MSSQL - Param = 1, Name = Result, Type =
fldINT32, Precision = 0, Scale = 0, Data = NULL
5       11:59:09  SQL Misc: MSSQL - Set statement type
6       11:59:09  SQL Execute: MSSQL -  :Result =
dbo.UnGenUniqueCallHistoryID;1 :InputInt,
7       11:59:09  SQL Error: MSSQL -
8       11:59:09  SQL Error: MSSQL - Unmapped SQL Error Code: 0
9       11:59:09  SQL Stmt: MSSQL - Reset

Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
Var
   SProc: TStoredProc;

Begin
   try
     SProc := TStoredProc.Create(Nil);
     SProc.DatabaseName := 'MyDataBase';
     SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
     SProc.Params.CreateParam(ftInteger, 'InPutInt', ptInput);
     SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
     SProc.ParamByName('InputInt').AsInteger := MyValue;
     SProc.ExecProc;
     Result := Sproc.ParamByName('Result').AsInteger;   // Returns the value
that was returned from Stored Procedure
     if Result > 0 Then
        Begin
           ShowMessage('This is the Old Call History Number Returned From
Server ' + IntToStr(Result));
           CallHistNo := Result;
        End
     Else
        Begin
           ShowMessage('The Call History Number Could Not Be Decreased' +
Char(13) +
            ' Because of another User Increased It On Server');
           // CallHistNo Stays the same as was - could Set Global Flag Here
           // Result will Equal -1 Self Induced - Error from Server

        End;

   finally
     SProc.Free;

   End;

End;

HERE IS THE ACTUAL STORED PROCEDURE - NOTE: THE STORED PROCEDURE RUN BY ITS
SELF IN ISQL QUERY TOOL.
if exists (select * from sysobjects where id =
object_id('dbo.UnGenUniqueCallHistoryID') and sysstat & 0xf = 4)
 drop procedure dbo.UnGenUniqueCallHistoryID
GO

CREATE PROCEDURE UnGenUniqueCallHistoryID(
@TestUID Int)
As
Begin

 Declare @UID Int
 Declare  @MessageString VarChar(255)

 Print 'Stored Procedure - UNGENUNIQUECALLHISTORYID'

 Begin Tran
 Select @UID = Last_Call_History_No
  From SystemInfo
 if (@TestUID = @UID)
     Begin
               Select @MessageString = 'This CallHistoryNo Can Be decreased
from the server :' + CONVERT(Char(20),@UID)
         Print @MessageString
         update SystemInfo
         set Last_Call_History_No = Last_Call_History_No - 1          Select
@UID = Last_Call_History_No
         From SystemInfo

     End
 Else
     Begin
               Select @MessageString = 'This CallHistoryNo Can NOT Be
decreased from the server :' + CONVERT(Char(20),@UID)
         Print @MessageString
  Select @UID = -1
     End

 Commit Tran

 Return @UID
End
GO

 

Re:Stored Procedure - Passing Argument in to a Stored Procedure


But What did work was the matching of the exact arguments in the stored
procedure used for the ptInput which was @TestUID so the secret is to use
the names the same in both the stored procedure and the call function in
delphi example below. Note: the Result it does not matter but I will still
have to check on the information about SQL return -1 so do not return -1 I
will have to run some test but thanks because now I know what I must do.
THANKS

     SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
     SProc.Params.CreateParam(ftInteger, '@TestUID', ptInput);
     SProc.ParamByName('@TestUID').AsInteger := MyValue;
     SProc.ExecProc;                                       ERROR DOES NOT
HAPPEN

Frank H. Shaw <fs...@runestones.com> wrote in message
news:8eddgl$rl613@bornews.borland.com...

Quote

> Hi I have struggling with stored procedure problems with SQL 6.5 most of
it
> using stored procedure is very new to me.

> I have tried it both ways and have different problems with each way. Note:
I
> want to do it run time is my end result.

> I set up in my project in my datamodule the following when I set the
active
> properity true I get the error message - Error Creating cursor handle

> See If you can follow what I did methord 1

> Name - properity was set auto to StoredProc1

> Step One - Drop Stored Procedure control into my datamodule which is unit2
> in my project

> Step Two - Set The Database Properity to MyDatabase

> Step Three - ParambindMode default to pbByName

> Step Four - StoredProcName was set by select in list of stored procedure
the
> name

>   dbo.UnGenUniqueCallHistoryID;1

> Step Five - Params using the edit two varables displayed Result, @TestUID
> when selected on each one set the following:

>  DataType ftInteger
>  Name Result
>  Paramtype ptResult

>  DataType FtInteger
>  Name @TestUID
>  ParamType ptInput
>  Value 0
>  Type Integer

> Step Six - Set the active properity to True

> HERE IS WHERE I GOT THE ERROR MESSAGE -

> This is methord 2 where I get a different error message - I even tried to
> change the paramters around but still same error.

> When I do it in code I get the Error message - general SQL Error

> 1       17:06:35  SQL Prepare: MSSQL -  :1 =

dbo.UnGenUniqueCallHistoryID;1

- Show quoted text -

Quote
> :2,
> 2       17:06:35  SQL Misc: MSSQL - Set stored procedure on or off
> 3       17:06:35  SQL Data In: MSSQL - Param = 1, Name = Result, Type =
> fldINT32, Precision = 0, Scale = 0, Data = NULL
> 4       17:06:35  SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
> fldINT32, Precision = 0, Scale = 0, Data = 659
> 5       17:06:35  SQL Misc: MSSQL - Set statement type
> 6       17:06:35  SQL Execute: MSSQL -  :Result =
> dbo.UnGenUniqueCallHistoryID;1 :InputInt,
> 7       17:06:35  SQL Error: MSSQL -
> 8       17:06:35  SQL Error: MSSQL - Unmapped SQL Error Code: 0
> 9       17:06:35  SQL Stmt: MSSQL - Reset

> Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
> Var
>    SProc: TStoredProc;

> Begin
>    try
>      SProc := TStoredProc.Create(Nil);
>      SProc.DatabaseName := 'MyDataBase';
>      SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
>      // Reverese The order Parameters - Fix - Test Now - Still Fails
>      SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
>      SProc.Params.CreateParam(ftInteger, 'InputInt', ptInput);
>      SProc.ParamByName('InputInt').AsInteger := MyValue;
>      SProc.ExecProc;
>      Result := Sproc.ParamByName('Result').AsInteger;   // Returns the
value
> that was returned from Stored Procedure
>      if Result > 0 Then
>         Begin
>            ShowMessage('This is the Old Call History Number Returned From
> Server ' + IntToStr(Result));
>            CallHistNo := Result;
>         End
>      Else
>         Begin
>            ShowMessage('The Call History Number Could Not Be Decreased' +
> Char(13) +
>             ' Because of another User Increased It On Server');
>            // CallHistNo Stays the same as was - could Set Global Flag
Here
>            // Result will Equal -1 Self Induced - Error from Server

>         End;

>    finally
>      SProc.Free;

>    End;

> End;

> THIS IS THE OLD LOG HERE AND OLD FUNCTION

> 1       11:59:09  SQL Prepare: MSSQL -  :1 =

dbo.UnGenUniqueCallHistoryID;1

- Show quoted text -

Quote
> :2,
> 2       11:59:09  SQL Misc: MSSQL - Set stored procedure on or off
> 3       11:59:09  SQL Data In: MSSQL - Param = 2, Name = InputInt, Type =
> fldINT32, Precision = 0, Scale = 0, Data = 658
> 4       11:59:09  SQL Data In: MSSQL - Param = 1, Name = Result, Type =
> fldINT32, Precision = 0, Scale = 0, Data = NULL
> 5       11:59:09  SQL Misc: MSSQL - Set statement type
> 6       11:59:09  SQL Execute: MSSQL -  :Result =
> dbo.UnGenUniqueCallHistoryID;1 :InputInt,
> 7       11:59:09  SQL Error: MSSQL -
> 8       11:59:09  SQL Error: MSSQL - Unmapped SQL Error Code: 0
> 9       11:59:09  SQL Stmt: MSSQL - Reset

> Function UnGetUniqueCallHistNo(MyValue: Integer): Integer;
> Var
>    SProc: TStoredProc;

> Begin
>    try
>      SProc := TStoredProc.Create(Nil);
>      SProc.DatabaseName := 'MyDataBase';
>      SProc.StoredProcName := 'dbo.UnGenUniqueCallHistoryID;1';
>      SProc.Params.CreateParam(ftInteger, 'InPutInt', ptInput);
>      SProc.Params.CreateParam(ftInteger, 'Result', ptResult);
>      SProc.ParamByName('InputInt').AsInteger := MyValue;
>      SProc.ExecProc;
>      Result := Sproc.ParamByName('Result').AsInteger;   // Returns the
value
> that was returned from Stored Procedure
>      if Result > 0 Then
>         Begin
>            ShowMessage('This is the Old Call History Number Returned From
> Server ' + IntToStr(Result));
>            CallHistNo := Result;
>         End
>      Else
>         Begin
>            ShowMessage('The Call History Number Could Not Be Decreased' +
> Char(13) +
>             ' Because of another User Increased It On Server');
>            // CallHistNo Stays the same as was - could Set Global Flag
Here
>            // Result will Equal -1 Self Induced - Error from Server

>         End;

>    finally
>      SProc.Free;

>    End;

> End;

> HERE IS THE ACTUAL STORED PROCEDURE - NOTE: THE STORED PROCEDURE RUN BY
ITS
> SELF IN ISQL QUERY TOOL.

> if exists (select * from sysobjects where id =
> object_id('dbo.UnGenUniqueCallHistoryID') and sysstat & 0xf = 4)
>  drop procedure dbo.UnGenUniqueCallHistoryID
> GO

> CREATE PROCEDURE UnGenUniqueCallHistoryID(
> @TestUID Int)
> As
> Begin

>  Declare @UID Int
>  Declare  @MessageString VarChar(255)

>  Print 'Stored Procedure - UNGENUNIQUECALLHISTORYID'

>  Begin Tran
>  Select @UID = Last_Call_History_No
>   From SystemInfo
>  if (@TestUID = @UID)
>      Begin
>                Select @MessageString = 'This CallHistoryNo Can Be
decreased
> from the server :' + CONVERT(Char(20),@UID)
>          Print @MessageString
>          update SystemInfo
>          set Last_Call_History_No = Last_Call_History_No - 1
Select
> @UID = Last_Call_History_No
>          From SystemInfo

>      End
>  Else
>      Begin
>                Select @MessageString = 'This CallHistoryNo Can NOT Be
> decreased from the server :' + CONVERT(Char(20),@UID)
>          Print @MessageString
>   Select @UID = -1
>      End

>  Commit Tran

>  Return @UID

> End
> GO

Other Threads