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
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
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
ASKER
I really appreciate you help as I’m a novice in Transact SQL.
Thanks in advance