Operand type clash: int is incompatible with cursor

Question

Operand type clash: int is incompatible with cursor

Akhil Kumar CHEKKARRAJ 20

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

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

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.

Akhil Kumar CHEKKARRAJ 20 Reputation points

2023-04-25T08:13:29.6733333+00:00

Hello, thanks for the reply. My use case is to return data from two tables from the stored procedure, I have tried creating a function with return type as a table and calling the function from the stored procedure but then I faced some syntactical errors while creating the function. Then I was able to achieve the use case using cursors, however, when I try to execute I was facing a different error.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-25T20:06:10.38+00:00

Maybe we should see if we can help you with that syntactic issue with the function.

As I said, cursors is something you use rarely, and cursor parameters even more rarely.

And they can be called, but you need to write the call yourself and not have SSMS to generate it for you.
Akhil Kumar CHEKKARRAJ 20 Reputation points

2023-04-26T06:57:53.6266667+00:00

Thanks for your reply, I have tried to pass my select query as input to function because of which it was not working. However, now I am just passing the parameters to function from the procedure and was able to fetch the data without having any dynamic queries. Finally, it's a bit strange, as this is a basic feature that should be available in SQL server which has not been fixed for all these years.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-26T20:11:12.1133333+00:00

I will have to beg to differ. A cursor parameter is not a basic feature. It is something very odd that very few people even know about.

Answer 2

LiHongMSFT-4306 31,566

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.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-25T20:02:29.0833333+00:00

Eh, SSMS does not use JDBC.
Akhil Kumar CHEKKARRAJ 20 Reputation points

2023-04-26T06:59:06.4733333+00:00

Thank you for the reply, I have used a function with table as return type and was able to achieve my use case. I will close the request. I was trying to invoke from Tibco BW which was using JDBC driver and had similar issue. Anyway its fixed now.

Share via

Operand type clash: int is incompatible with cursor

1 additional answer

Your answer