Database First Exception in Stored Procedure

Question

My Stored Procedure is::

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AdvancedSearch] 
    @CompanyID bigint,
    @BookingReference nvarchar(15)

AS
Begin

declare @ExecuteSQL nvarchar(2000)
declare @WhereSQL nvarchar(2000)

if rtrim(isnull(@BookingReference,''))<>''
begin
set @WhereSQL = concat(@WhereSQL,' and
exists(select top 1 null from document (nolock) where document.dossierID=DossierHeader.DossierID and bookingreference like ''%' , @BookingReference , '%'' and Document.CompanyID=',@CompanyID,')')
end

set @ExecuteSQL=concat('
select top 10 dossier.DisplayID,dossierid,CustomerID,isnull(Customer.ShortName,Customer.[CustomerName]) as Customer,DossierHeader.InvoiceNumber,
from DossierHeader
join Customer
on DossierHeader.CustomerID=Customer.ID
where 
DossierHeader.CompanyID=',@CompanyID,@WhereSQL)

EXEC SP_EXECUTESQL @ExecuteSQL

end

And when I am using it in my code then on Debug I am getting some exception like::

How can I resolve this Problem?

Answer 1

You should change your stored procedure to table value function if you want the result of your nested select returned (stored procedures return execution return codes or output parameters).

The difference:

Function vs. Stored Procedure in SQL Server

And the support of table valued functions by Entity Framework:

http://blogs.msdn.com/b/efdesign/archive/2011/01/21/table-valued-function-support.aspx