DBExec - Missing parameters in function call -min. 3 expecte

[Jamball77]

I have a simple script that calls a SQL stored procedure using DBExec since my stored procedure returns nothing.

When I run/debug and hit the DBExec line I get the error: Missing parameters in function call (minimum of 3 expected)

How do I interpret this error/fix my problem? I thought that DBExec is for executing SQL code without return parameters (compared to DBQuery which does).


Input>CO_ID_string,Enter the CO_ID,
DBConnect>DSN=......, dbH
LET>SQL= DECLARE @RC int DECLARE @CO_NUMB varchar (15) SET @CO_NUMB = 'CO_ID_string' EXECUTE @RC =
dbo.SP_KIC_CO_Entry_USER_9 @CO_NUMB

debugger makes it fine to here and I can see the value updated for my input variable that will pass into the Stored Procedure.

DBExec>dbH,SQL
DBClose>dbH


I successfully execute the Stored Procedure in MS SQL:

DECLARE @RC int
DECLARE @CO_NUMB varchar (15)
SET @CO_NUMB = '147113'
EXECUTE @RC = dbo.SP_KIC_CO_Entry_USER_9 @CO_NUMB


Many thanks for the debugger use tutorial and
// https://www.mjtnet.com/blog/2008/04/15/ ... functions/

Both are very useful.

[JRL]

The function requires three parameters.

DBExec>database_reference,SQL_Statement,result

You only have two.

DBExec>dbH,SQL

Just add the result parameter on the end then ignore it if you're not interested in its value.

[Jamball77]

Ah, I see now. It's returning the number of rows affected.

Works. Many thanks.

quoting from: https://www.mjtnet.com/blog/2008/04/15/ ... functions/

Modifying Data

To perform any SQL statement that does not return data use the DBExec command. E.g. DBExec can be used for a DELETE, INSERT or UPDATE query. DBExec again takes a database reference returned by DBConnect, the SQL statement, and returns the number of rows affected:

1
2
Let>SQL=DELETE FROM CUSTOMERS WHERE CUSTID=1532
DBExec>dbH,SQL,rowsAffected

[Jamball77]

It's common practice in stored procedures to suppress the row count returned by
updating your stored procedure by using "SET NOCOUNT ON"

EXAMPLE:

[snippet=]ALTER PROCEDURE [dbo].[storedprocedure_name]
@YR_CDE [char] (4)
,@TRM_CDE [char] (2)
,@Begin_DTE [datetime]
,@End_DTE [datetime]
AS

SET NOCOUNT ON; -- this line prevents return of row count with result set[/snippet]