[RESOLVED]unable to handle procedure with table result and output parameter

how to handle two out puts from procedure

My Procedure

ALTER procedure [dbo].[getmembers]

@startRowIndex int,
@maximumRows int,
@crteria varchar(max),
@Count int = 0 output
as
declare @sqlst as varchar(max),@MaxRow int
set @MaxRow=@startRowIndex + @maximumRows;

declare @tempTbl table(
pid bigint ,
 profiledate datetime,  
 fname varchar(250) ,
 lname varchar(250) ,
 gender varchar(1) ,
countryid bigint,
whoami varchar(MAX), 
lookingfor varchar(MAX),
stateid bigint, 
cityid  bigint,
photo varchar(250),
headline varchar(250) ,
rowid int
)
 select @sqlst = 'select * from (select
 pid ,
 profiledate,  
 fname ,
 lname ,
 gender,
countryid,
whoami, 
lookingfor,
stateid , 
cityid ,
(select TOP 1 photo from [photo] where photo.pid = Profile.pid) as photo,
headline ,

   ROW_NUMBER() OVER(ORDER BY profiledate DESC) AS rowid 
 from [Profile]  where  ' + @crteria + ' ) as kk'
 
 
insert into @tempTbl EXEC(@sqlst + ' where rowid > '+@startRowIndex+' AND rowid <= ('+ @MaxRow + ')')


select @sqlst='select '''+CAST(@Count AS VARCHAR(MAX))+'''= Count(*) from [Profile] where '+ @crteria 

EXEC(@sqlst) 
select * from @tempTbl
Return 

which returns a table and Output parameter

but my MVC entity is unable to handle it

my controller

public ActionResult BrouseAllMem(int? page,getmembersearch gt)
        {

            int currentPageIndex = page.HasValue ? page.Value : 1;



           
           
            IList<getmembers_Result1> members = this.getAllmembers;

           

            if(gt.countryid > 0)
            {
                country = " AND countryid=" + gt.countryid;
            }
            if (gt.stateid  > 0)
            {
                state = " AND stateid=" + gt.stateid;
            }
            if (gt.cityid  > 0)
            {
                city = " AND cityid=" + gt.cityid;
            }


            criteria = "susp = 'N'  " + city + country + state ;

            System.Data.Entity.Core.Objects.ObjectParameter Count = new System.Data.Entity.Core.Objects.ObjectParameter("Count", typeof(int));
           // output = 0;

            gt.data  = db.getmembers(currentPageIndex, defaultPageSize, criteria, Count).ToList()  ;

            gt.pagesize = defaultPageSize;
            
            gt.TotalCount = Convert.ToInt64(Count);

           // members = mm.OrderByDescending(p => p.pid).ToPagedList(currentPageIndex, defaultPageSize);
 
          
            return View(gt);
        }

ok now it is throwing exception when i m trying to get count in verible

Server Error in '/' Application.

Unable to cast object of type 'System.Data.Entity.Core.Objects.ObjectParameter' to type 'System.IConvertible'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.Entity.Core.Objects.ObjectParameter' to type 'System.IConvertible'.

Source Error: 


Line 395:            gt.pagesize = defaultPageSize;
Line 396:            
Line 397:            gt.TotalCount = Convert.ToInt64(Count);
Line 398:
Line 399:           // members = mm.OrderByDescending(p => p.pid).ToPagedList(currentPageIndex, defaultPageSize);

ok my bad i just have to

use count.value

Tongue Out

Hi pjpatil,

For this issue, you also could refer to:

# Execute a Query Using a Stored Procedure with In and Out Parameters

http://msdn.microsoft.com/en-us/library/vstudio/bb896334(v=vs.100).aspx

Best Regards

Starain

Leave a Reply