asked on

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

james5150

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

bolicat

ASKER

Please can ypu provide an example

ASKER CERTIFIED SOLUTION

james5150

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bolicat

ASKER

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

james5150

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

bolicat

ASKER

thnaks a lot