Link to home
Start Free TrialLog in
Avatar of bolicat
bolicat

asked on

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

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

ASKER CERTIFIED SOLUTION
Avatar of jrb1
jrb1
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bolicat
bolicat

ASKER

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
Avatar of bolicat

ASKER

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
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