?
Solved

error when try to execute a procedure Operand type clash: varchar is incompatible with cursor

Posted on 2005-03-03
4
Medium Priority
?
2,075 Views
Last Modified: 2008-01-09
Please help, when execute the following procedure I'm getting the follwoing error
I'm working on this since yesterday and can't make it work.

I'm new in SQL any help will be appreciate, my procedure is bellow.

Server: Msg 206, Level 16, State 2, Procedure dbfind_status, Line 0
Operand type clash: varchar is incompatible with cursor

DECLARE @ReturnCode INT
DECLARE @irowcntvar INT
DECLARE @itailpntrvar INT
DECLARE @iheadpntrvar INT
DECLARE @io_dbfind_var cursor
SET @io_dbfind_var = "SELECT IMAGE_RECNBR FROM VSEL_VNDR_PYMT_D WHERE VP_PYMT_SCHD ='Q' AND VP_DAYS_FLOAT = ' ' ORDER BY VNDR_PYMT_DATE"
EXEC @ReturnCode = dbo.dbfind_status @dbfind_cursor = '@io_dbfind_var',
     @irowcnt = @irowcntvar output,
     @itailpntr = @itailpntrvar output,
     @iheadpntr = @iheadpntrvar output  

create procedure dbo.dbget4_status
    @dbget4_cursor cursor varying output,
    @irecnum int,
    @ibackpntr int output,
    @ifrwdpntr int output
    as
      set nocount on
      declare @icurnum int
      set @ibackpntr = 0
      set @ifrwdpntr = 0
      open @dbget4_cursor
      fetch next from @dbget4_cursor into @icurnum
      while @@fetch_status = 0 and @icurnum != @irecnum
        fetch next from @dbget4_cursor into @icurnum
      if @icurnum = @irecnum
        begin
          fetch relative -1 from @dbget4_cursor into @ibackpntr
          fetch relative 2 from @dbget4_cursor into @ifrwdpntr
        end
      close @dbget4_cursor
      deallocate @dbget4_cursor
    return

0
Comment
Question by:bolicat
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 1500 total points
ID: 13454649
A cursor declation looks like this:

DECLARE @io_dbfind_var cursor FOR SELECT IMAGE_RECNBR FROM VSEL_VNDR_PYMT_D WHERE VP_PYMT_SCHD ='Q' AND VP_DAYS_FLOAT = ' ' ORDER BY VNDR_PYMT_DATE

Here is a good example of it's use (scroll toward the bottom of the page)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp
0
 

Author Comment

by:bolicat
ID: 13459063
Please it is possible to show me an example of how my execute syntax is supposed to look?

I really appreciate you help as I’m a novice in Transact SQL.

Thanks in advance
0
 

Author Comment

by:bolicat
ID: 13479764
jrb1,

This is what I'm trying to excecute and getting error

Server: Msg 206, Level 16, State 2, Procedure dbfind_status, Line 0
Operand type clash: varchar is incompatible with cursor

DECLARE @ReturnCode INT
DECLARE @irowcntvar INT
DECLARE @itailpntrvar INT
DECLARE @iheadpntrvar INT
DECLARE @io_dbfind_var CURSOR
SET @io_dbfind_var = "SELECT IMAGE_RECNBR FROM VSEL_VNDR_PYMT_D WHERE VP_PYMT_SCHD ='Q' AND VP_DAYS_FLOAT = ' ' ORDER BY VNDR_PYMT_DATE"
EXEC @ReturnCode = dbo.dbfind_status @dbfind_cursor = '@io_dbfind_var',
     @irowcnt = @irowcntvar output,
     @itailpntr = @itailpntrvar output,
     @iheadpntr = @iheadpntrvar output

Please advice.

bolicat
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13757637
Are you still having a problem?  Was cleaning out my folder and found this:

DECLARE @ReturnCode INT
DECLARE @irowcntvar INT
DECLARE @itailpntrvar INT
DECLARE @iheadpntrvar INT
DECLARE io_dbfind_var CURSOR For
             SELECT IMAGE_RECNBR
             FROM VSEL_VNDR_PYMT_D
             WHERE VP_PYMT_SCHD ='Q'
             AND VP_DAYS_FLOAT = ' '
             ORDER BY VNDR_PYMT_DATE

EXEC @ReturnCode = dbo.dbfind_status @dbfind_cursor = @io_dbfind_var,
     @irowcnt = @irowcntvar output,
     @itailpntr = @itailpntrvar output,
     @iheadpntr = @iheadpntrvar output
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question