Operand type clash: int is incompatible with cursor

Akhil Kumar CHEKKARRAJ 20 Reputation points
2023-04-24T19:08:24.59+00:00

Hello, I have a stored procedure with cursor as return parameter, I have created the procedure and compiled it. However when I try to execute using SSMS tool the return type for the cursor is being shown as INT and throwing below error upon passing the params "Operand type clash: int is incompatible with cursor". I am bit new to SQL server, please help. Pasting the script to execute the procedure.

DECLARE	@return_value int,
		@ALLFARESONWRS int, --cursor
		@ALLFARESRETRS int --Cursor

EXEC	@return_value = FARES_SRVCE_PRC
		@TABLENAME = N'FARES_TABLE_A',
		@ORIGIN = N'A',
		@DESTINATION = N'B',
		@TRAVELDATE = N'2022-01-02',
		@RETURNDATE = N'2022-01-02',
		@FAREFAMILY = N'BEST',
		@ALLFARESONWRS = @ALLFARESONWRS OUTPUT,
		@ALLFARESRETRS = @ALLFARESRETRS OUTPUT

BR, Akhil

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,060 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2023-04-24T20:30:00.5066667+00:00

    You are correct. I am able to reproduce this behaviour.

    You can file bugs on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0.

    However, I am not sure that this will pass the triage bar. First of all, cursor parameters are very rarely used. Overall cursors is something you should use sparingly. Do you have any actual use case, or are you just playing around?

    Next, I am not sure that it is meaningful to use the Execute Stored Procedure to run a procedure that takes an output parameter, and even more so a CURSOR parameter. That dialog is not able to do anything with the cursor. Normally, you would run such a procedure from a query window.


1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 24,356 Reputation points
    2023-04-25T02:30:50.9133333+00:00

    Hi @Akhil Kumar CHEKKARRAJ

    Referring to this doc for more details: Using a stored procedure with output parameters.

    This is caused by a known limitation of the MSSQL JDBC Driver. User's image

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.