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

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Jamball77
Newbie
Posts: 14
Joined: Tue Aug 11, 2015 2:25 pm

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

Post by Jamball77 » Tue Aug 11, 2015 4:03 pm

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.

User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Re: DBExec - Missing parameters in function call -min. 3 exp

Post by JRL » Tue Aug 11, 2015 4:29 pm

The function requires three parameters.
DBExec help wrote:DBExec>database_reference,SQL_Statement,result
You only have two.
Jamball77 wrote:DBExec>dbH,SQL
Just add the result parameter on the end then ignore it if you're not interested in its value.

Jamball77
Newbie
Posts: 14
Joined: Tue Aug 11, 2015 2:25 pm

Re: DBExec - Missing parameters in function call -min. 3 exp

Post by Jamball77 » Tue Aug 11, 2015 7:12 pm

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
Newbie
Posts: 14
Joined: Tue Aug 11, 2015 2:25 pm

Re: DBExec - Missing parameters in function call -min. 3 exp

Post by Jamball77 » Mon Feb 06, 2017 3:47 pm

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]

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts