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

x
  • 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)
    as
      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
    return
0
bolicat
Asked:
bolicat
  • 3
  • 3
1 Solution
 
james5150Commented:
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

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

James
0
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 @ReturnCode INT
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
0
 
james5150Commented:
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.

Jim

James
0
 
bolicatAuthor Commented:
thnaks a lot
0

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