ASP.NET MVC Stored Procedure

Question

I am using a ms sql stored procedure to get a set of records , when i am adding this stored procedure to my LinqToSql class and using it my repository it shows like it is returning a int value but it should be returning a set of rows. Is there some thing wrong with my stored procedure or some thing else???

ALTER PROCEDURE [dbo].[GetDocumentsAdvancedSearch]
    -- Add the parameters for the stored procedure here
      @SDI  CHAR(10) = NULL
     ,@Client CHAR(4) = NULL
     ,@AccountNumber VARCHAR(20) = NULL
     ,@Address VARCHAR(300)  = NULL
     ,@StartDate DATETIME = NULL
     ,@EndDate DATETIME = NULL
     ,@Job INT = NULL
     ,@Invoice INT = NULL
     ,@Amount MONEY = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   -- DECLARE
    DECLARE @Sql        NVARCHAR(4000)
    DEClARE @ParamList  NVARCHAR(4000)
    DECLARE @AssociaID VARCHAR(6)
    --DECLARE 
    SET @AssociaID = 000000
    SELECT  @Sql = 'SELECT 
                    DISTINCT ISNULL(Documents.DocumentID, NULL) 
                   ,Person.Name1
                   ,Person.Name2
                   ,Person.Street1
                   ,Person.Street2
                   ,Person.CityStateZip
                   ,ISNULL(Person.ReferenceID,NULL)
                   ,ISNULL(Person.AccountNumber,NULL)
                   ,ISNULL(Person.HasSetPreferences,0)
                   ,Documents.Job
                   ,Documents.SDI
                   ,Documents.Invoice
                   ,ISNULL(Documents.ShippedDate,NULL)
                   ,ISNULL(Documents.DocumentPages,NULL)
                   ,Documents.DocumentType
                   FROM
                   Person
                   LEFT OUTER JOIN Documents ON Person.PersonID = Documents.PersonID
                   LEFT OUTER JOIN DocumentType ON Documents.DocumentType = DocumentType.DocumentType
                   LEFT OUTER JOIN Addresses   ON Person.PersonID = Addresses.PersonID
                   WHERE '                   

   IF NOT(@SDI IS NULL)
   SELECT @Sql = @Sql + ' Documents.SDI IN ('+@sdi+')' 
   IF NOT(@Client IS NULL)
   SELECT @Sql = @Sql + ' OR (Person.AssociationID = ' + @AssociaID + ' AND Person.Client ='+ @Client+')'   
   IF NOT(@AccountNumber IS  NULL)
   SELECT @Sql = @Sql + ' AND Person.AccountNumber LIKE ' + @AccountNumber
   IF NOT(@Address IS  NULL)
   SELECT @Sql = @Sql + ' AND Person.Name1 LIKE' + @Address + 'AND Person.Name2 LIKE' + @Address + ' AND Person.Street1 LIKE' + @Address + ' AND Person.Street2 LIKE' + @Address + ' AND Person.CityStateZip LIKE' + @Address
   IF NOT(@StartDate IS  NULL)
   SELECT @Sql = @Sql + ' AND Documents.ShippedDate >=' +@StartDate
   IF NOT(@EndDate IS NULL)
   SELECT @Sql = @Sql + ' AND Documents.ShippedDate <=' +@EndDate
   IF NOT(@Job IS NULL)
   SELECT @Sql = @Sql + ' AND Documents.Job =' +@Job
   IF NOT(@Invoice IS NULL)
   SELECT @Sql = @Sql + ' AND Documents.Invoice =' +@Invoice
   IF NOT(@Amount IS NULL)
   SELECT @Sql = @Sql + ' AND Documents.Amount =' +@Amount
    -- Insert statements for procedure here
    --PRINT @Sql
    SELECT @ParamList = '@Psdi CHAR(10),@PClient CHAR(4),@PAccountNumber VARCHAR(20),@PAddress VARCHAR(300),@PStartDate DATETIME ,@PEndDate DATETIME,@PJob INT,@PInvoice INT,@PAmount Money '
    EXEC SP_EXECUTESQL @Sql,@ParamList,@Sdi,@Client,@AccountNumber,@Address,@StartDate,@EndDate,@Job,@Invoice,@Amount
    --PRINT @Sql

END



[Function(Name="dbo.GetDocumentsAdvancedSearch")]
        public int GetDocumentsAdvancedSearch([Parameter(Name="SDI", DbType="Char(10)")] string sDI, [Parameter(Name="Client", DbType="Char(4)")] string client, [Parameter(Name="AccountNumber", DbType="VarChar(20)")] string accountNumber, [Parameter(Name="Address", DbType="VarChar(300)")] string address, [Parameter(Name="StartDate", DbType="DateTime")] System.Nullable<System.DateTime> startDate, [Parameter(Name="EndDate", DbType="DateTime")] System.Nullable<System.DateTime> endDate, [Parameter(Name="Job", DbType="Int")] System.Nullable<int> job, [Parameter(Name="Invoice", DbType="Int")] System.Nullable<int> invoice, [Parameter(Name="Amount", DbType="Money")] System.Nullable<decimal> amount)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), sDI, client, accountNumber, address, startDate, endDate, job, invoice, amount);
            return ((int)(result.ReturnValue));
        }

Answer 1

My guess would be that since the SQL is dynamic, LINQ isn't able to determine exactly what the procedure will be returning.

Best workaround for this would probably be to just hand-edit the .dbml file

ETA: If you aren't sure what to put in the .dbml file, another option might be to "trick" it into figuring out what you are returning from your sproc by putting some SQL inside a block that will never actually execute in practice:

IF ( FALSE )
BEGIN
  SELECT 
  DISTINCT ISNULL(Documents.DocumentID, NULL) 
  ,Person.Name1
  ,Person.Name2
  ,Person.Street1
  ,Person.Street2
  ,Person.CityStateZip
  ,ISNULL(Person.ReferenceID,NULL)
  ,ISNULL(Person.AccountNumber,NULL)
  ,ISNULL(Person.HasSetPreferences,0)
  ,Documents.Job
  ,Documents.SDI
  ,Documents.Invoice
  ,ISNULL(Documents.ShippedDate,NULL)
  ,ISNULL(Documents.DocumentPages,NULL)
  ,Documents.DocumentType
  FROM
   Person
   LEFT OUTER JOIN Documents ON Person.PersonID = Documents.PersonID
   LEFT OUTER JOIN DocumentType ON Documents.DocumentType = DocumentType.DocumentType
   LEFT OUTER JOIN Addresses   ON Person.PersonID = Addresses.PersonID
END

Answer 2

Create a class manually on your DataContext layout (DBML) that contains the right properties that the stored procedure returns. Change the return type of the stored procedure in its properties.

Answer 3

Linq to SQL does not recognize EXEC SP_EXECUTESQL -- you'll need to define the output manually.

http://social.msdn.microsoft.com/forums/en-US/linqtosql/thread/d7220c7a-d194-4d92-96dd-a3def25e9be9/