trying to pass parms using execute procedure and getting errors

Posted on 2005-03-02
Medium Priority
Last Modified: 2008-01-09
I'm trying to pass the following parms using execute to my procedure and compile but when I try to run I'm getting the following errors.

What I'm doing wrong, please help.

  execute dbo.dbfind_status
  Declare @SQL varchar(2000),
          @tblCust varchar(20)
  Set @sql = 'SELECT CustomerID, CustomerName FROM ' + @tblCust
  Exec @sql

Server: Msg 201, Level 16, State 4, Procedure dbfind_status, Line 0
Procedure 'dbfind_status' expects parameter '@dbfind_cursor', which was not supplied.
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure ''.

  create procedure dbo.dbfind_status
    @dbfind_cursor cursor varying output,
    @irowcnt int output,
    @itailpntr int output,
    @iheadpntr int output,
    @io_image_recnbr varchar(2000)
--    @io_tblName varchar(50),
--    @io_where varchar(200),
--    @io_orderby varchar(250)
      set nocount on
      set @irowcnt = 0
      set @itailpntr = 0
      set @iheadpntr = 0
--   DECLARE @SQL varchar(600)
--     SET @SQL = 'SELECT io_image_recnbr FROM ' + @io_tblName + @io_where + 'order by' + @io_orderby
      open @dbfind_cursor
      set @irowcnt = @@cursor_rows
      fetch last from @dbfind_cursor into @itailpntr
      fetch first from @dbfind_cursor into @iheadpntr
      close @dbfind_cursor
      deallocate @dbfind_cursor
Question by:bolicat
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
  • 3
  • 3

Expert Comment

ID: 13439546
Your not providing any parameters for the stored proc

dbfind_status  parameter1, Parameter2 etc...

where Parameter1 is your input data also when executing a string you need to put it in brackets i.e.

Exec (@sql) otherwise it thinks its trying to call a stored procedure..

Hope this helps


Author Comment

ID: 13439736
Please can ypu provide an example

Accepted Solution

james5150 earned 1200 total points
ID: 13440291
Hi ,

Cos the first few parameters are outputs and the first is a cursor output

Declare them as

declare @MyCursor Cursor
declare @rowcnt int ,@itailpntr int, @iheadpntr int

Then call your stored proc as :

dbfind_status  @MyCursor OUT,@rowcnt OUT,@itailpntr OUT, @iheadpntr OUT, 'My Value'

The last Parameter is an input parameter so you have to provide a value for @io_image_recnbr so it would be 'My Value' where My Value is your input text , don't forget the ' ' quotes because its expecting a string.

Theres a microsoft article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp  that tells you all about Stored Proc Parameters and has a few examples.

Hope this helps

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 13441053
My this really help thanks a lot

here is my execute I'm getting now error

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

-- Declare the variables for the return code and output parameter.
DECLARE @irowcntvar INT
DECLARE @itailpntrvar INT
DECLARE @iheadpntrvar INT
DECLARE @io_input_var varchar(2000)

-- Execute the stored procedure and specify which variables
-- are to receive the output parameter and return code values.
EXEC @ReturnCode = dbo.dbfind_status @dbfind_cursor = "'@io_input_var'",
     @irowcnt = @irowcntvar output,
     @itailpntr = @itailpntrvar output,
     @iheadpntr = @iheadpntrvar output

Expert Comment

ID: 13443134
I think the line:

DECLARE @io_input_var varchar(2000)

should be :

DECLARE @io_input_var cursor

and your execute should be:

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

and that should work, because at the moment your trying to assign the value to a string and it should be assigned to a cursor.

A cursor is like a recordset or a table , don't forget you have to assign an input parameter for @io_image_recnbr at the end.



Author Comment

ID: 13444862
thnaks a lot

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 the fundamental information of how to create a table.
Suggested Courses

771 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