?
Solved

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

Posted on 2005-03-03
4
Medium Priority
?
1,938 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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