CODEWITHSUNDEEP
javasql-serverstored-procedures
Mehaboob Khan
Mehaboob Khan

Reputation: 363

Stored procedure in SQL Server expecting output parameter as input

I am calling a stored procedure from my Java code. And after some computation in the stored procedure, I am returning the status.

This is the stored procedure:

ALTER PROCEDURE [dbo].[SAMPLE_ENTRIES]
    (@ID INTEGER, 
     @PERIOD_ID INTEGER, 
     @RUN_ID INTEGER, 
     @TYPE CHAR, 
     @RESULT_OUTPUT varchar(100) OUTPUT)
/**Here goes some computation**/
IF @RESULT_OUTPUT IS NULL  
BEGIN   
   set @RESULT_OUTPUT = 'SUCCESS';    
   select @RESULT_OUTPUT as RESULT_OUTPUT;  
END; 

END

As you can see here I am passing 4 parameters to the stored procedure and the 5th parameter is the output. But I am getting an error, below is the error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'SAMPLE_ENTRIES' expects parameter '@RESULT_OUTPUT', which was not supplied.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

I am new to stored procedures.

Thanks for the help in advance.

Upvotes: 0

Views: 2221

Answers (1)

Thom A
Thom A

Reputation: 95561

A OUTPUT parameter is still an input parameter; there is no such thing as a "non-input" parameter with Stored Procedures. You still need to pass a variable to the parameter, as the value of the OUTPUT parameter will be assigned to that variable once the SP completes.

If you were using T-SQL to execute the SP, it would look like this:

CREATE PROC dbo.MyProc @MyParam int OUTPUT AS
BEGIN

    SET @MyParam = 1;
END;
GO

DECLARE @MyParam int;
EXEC dbo.MyProc @MyParam OUTPUT;

PRINT @MyParam; --1

DB<>Fiddle

Upvotes: 3

Related Questions