Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

trying to pass parms using execute procedure and getting errors

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
  • 3
  • 3
1 Solution
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

bolicatAuthor Commented:
Please can ypu provide an example
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

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.

bolicatAuthor Commented:
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
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.


bolicatAuthor Commented:
thnaks a lot

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now