title: How to get stored procedure to return results when called through Linq-to-SQL
body: <p>I have the following stored procedure:</p>
  
  <pre><code>CREATE PROC [Scanner].[proc_AllContracts_Sel_Grid_Filter]
      (@SearchText VARCHAR(500), 
       @CampaignStartDate DATETIME = NULL, 
       @CampaignEndate DATETIME = NULL,
       @isPNPSelected BIT = NULL,
       @isSparSelected BIT = NULL,
       @isClicksSelected BIT = NULL)
  AS  
      SET NOCOUNT ON;
      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  BEGIN
      SELECT       
          Sales.AccountManager.FirstName + N' ' + Sales.AccountManager.LastName AS AccountManagerName, 
          Sales.Contract.ContractNumber, 
          Sales.AccountManager.Code AS AccountManagerInitials_Code, 
          Client.Client.ClientName, 
          MAX(Sales.Burst.ChainName) AS ChainName,
          MAX(Sales.Burst.BrandName) AS BrandName, 
          MAX(Sales.Burst.MediaName) AS MediaType,  
          DATEDIFF(wk, Sales.vContractDates.FirstWeek, 
          Sales.vContractDates.LastInstallWeek) + 1 AS CampaignTotalRunningWeeks, 
          ISNULL(ISNULL(Sales.vContractDates.FirstWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate) AS CampaignFirstWeek, 
          ISNULL(ISNULL(Sales.vContractDates.LastInstallWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate) AS CampaignLastWeek, 
          DATEADD(wk, 1, ISNULL(ISNULL(Sales.vContractDates.LastInstallWeek, Sales.Contract.SignDate), 
          Sales.Contract.CreationDate)) AS ContractTerminationDate, 
          Sales.Contract.CreatedBy, 
          Sales.Contract.CreationDate, 
          Sales.Contract.Signed, 
          Sales.Contract.SignedBy, 
          Sales.Contract.SignDate, 
          Sales.Contract.ContractID,
          (SELECT COUNT(*) AS Expr1
           FROM Sales.StoreList WITH (NOLOCK) 
           INNER JOIN Sales.Burst AS b WITH (NOLOCK) ON Sales.StoreList.BurstID = b.BurstID
           WHERE (b.ContractID = Sales.Contract.ContractID) 
             AND (b.ChainName LIKE '%pick ''n pay%')) AS PNP_NumberOfStoresSelected,
         (SELECT COUNT(*) AS Expr1
          FROM Sales.StoreList AS StoreList_2 WITH (NOLOCK) 
          INNER JOIN Sales.Burst AS b WITH (NOLOCK) ON StoreList_2.BurstID = b.BurstID
          WHERE (b.ContractID = Sales.Contract.ContractID) 
            AND (b.ChainName LIKE '%Spar%')) AS Spar_NumberOfStoresSelected,
  (SELECT        COUNT(*) AS Expr1
  FROM            Sales.StoreList AS StoreList_1 INNER JOIN
                              Sales.Burst AS b ON StoreList_1.BurstID = b.BurstID
  WHERE        (b.ContractID = Sales.Contract.ContractID) AND (b.ChainName LIKE '%Click%')) AS Clicks_NumberOfStoresSelected, 
       -- SELECTED FOR SCANNER DATA
        ISNULL(CAST(COALESCE([PriorYear_isSelectedForScannerData], [PriorPeriod_isSelectedForScannerData], [PostPeriod_isSelectedForScannerData])AS BIT), 0) AS isSelectedForScannerData,
        CAST(COALESCE([PriorYear_SelectedForScannerData_Date], [PriorPeriod_SelectedForScannerData_Date], [PostPeriod_SelectedForScannerData_Date])AS DATETIME) AS SelectedForScannerData_Date,
       -- PNP READY
        ISNULL(CAST(COALESCE([PriorYear_isPNPDataReadyForPRC], [PriorPeriod_isPNPDataReadyForPRC], [PostPeriod_isPNPDataReadyForPRC])AS BIT),0) AS PNP_isDataReadyForPRC, 
       -- SPAR READY
        ISNULL(CAST(COALESCE([PriorYear_isSPARDataReadyForPRC], [PriorPeriod_isSPARDataReadyForPRC], [PostPeriod_isSPARDataReadyForPRC])AS BIT),0) AS SPAR_isDataReadyForPRC,  
       -- CLICKS READY
        ISNULL(CAST(COALESCE([PriorYear_isCLICKSDataReadyForPRC], [PriorPeriod_isCLICKSDataReadyForPRC], [PostPeriod_isCLICKSDataReadyForPRC])AS BIT),0) AS CLICKS_isDataReadyForPRC,
        -- READY DATES 
        CAST(COALESCE([PriorYear_PNPReadyDate], [PriorPeriod_PNPReadyDate], [PostPeriod_PNPReadyDate])AS DATETIME) AS PNP_ReadyDate,
        CAST(COALESCE([PriorYear_CLICKSReadyDate], [PriorPeriod_CLICKSReadyDate], [PostPeriod_CLICKSReadyDate])AS DATETIME) AS CLICKS_ReadyDate,
        CAST(COALESCE([PriorYear_SPARReadyDate], [PriorPeriod_SPARReadyDate], [PostPeriod_SPARReadyDate])AS DATETIME) AS SPAR_ReadyDate,
       -- PNP SUBMISSION
        ISNULL(CAST(COALESCE([PriorYear_isPNPDataSubmittedForPRC], [PriorPeriod_isPNPDataSubmittedForPRC], [PostPeriod_isPNPDataSubmittedForPRC])AS BIT),0) AS PNP_isDataSubmittedForPRC, 
        CAST(COALESCE([PriorYear_PNPSubmissionDate], [PriorPeriod_PNPSubmissionDate], [PostPeriod_PNPSubmissionDate])AS DATETIME) AS PNP_SubmissionDate,
       -- CLICKS SUBMISSION
        ISNULL(CAST(COALESCE([PriorYear_isCLICKSDataSubmittedForPRC], [PriorPeriod_isCLICKSDataSubmittedForPRC], [PostPeriod_isCLICKSDataSubmittedForPRC])AS BIT),0) AS CLICKS_isDataSubmittedForPRC, 
        CAST(COALESCE([PriorYear_CLICKSSubmissionDate], [PriorPeriod_CLICKSSubmissionDate], [PostPeriod_CLICKSSubmissionDate])AS DATETIME) AS CLICKS_SubmissionDate,
       -- SPAR SUBMISSION
        ISNULL(CAST(COALESCE([PriorYear_isSPARDataSubmittedForPRC], [PriorPeriod_isSPARDataSubmittedForPRC], [PostPeriod_isSPARDataSubmittedForPRC])AS BIT),0) AS SPAR_isDataSubmittedForPRC, 
        CAST(COALESCE([PriorYear_SPARSubmissionDate], [PriorPeriod_SPARSubmissionDate], [PostPeriod_SPARSubmissionDate])AS DATETIME) AS SPAR_SubmissionDate,
        ISNULL(COUNT([NovaDB].[Scanner].BarcodeRequest.ID),0) AS barcodecount
  
  FROM            Client.Client INNER JOIN
                          Sales.Contract WITH (NOLOCK) ON Client.Client.ClientID = Sales.Contract.ClientID INNER JOIN
                          Sales.AccountManager WITH (NOLOCK) ON Sales.Contract.AccountManagerID = Sales.AccountManager.AccountManagerID INNER JOIN
                          Sales.Burst WITH (NOLOCK) ON Sales.Contract.ContractID = Sales.Burst.ContractID INNER JOIN
                          Sales.vVisibleContractList WITH (NOLOCK) ON Sales.Contract.ContractID = Sales.vVisibleContractList.ContractID LEFT OUTER JOIN
                          [NovaDB].[Scanner].[DataRequest] WITH (NOLOCK) ON Sales.Contract.ContractNumber = [NovaDB].[Scanner].[DataRequest].[ContractNumber] LEFT OUTER JOIN
                          Sales.vContractDates WITH (NOLOCK) ON Sales.Contract.ContractID = Sales.vContractDates.ContractID LEFT OUTER JOIN [NovaDB].[Scanner].BarcodeRequest ON [NovaDB].[Scanner].[DataRequest].ID = [NovaDB].[Scanner].BarcodeRequest.DataRequestId
  WHERE        (Sales.Contract.Cancelled = 0) AND (Sales.Contract.Signed = 1)
  
                            --DYNAMIC WHERE CLAUSE FOR VARIOUS PARAMETERS WHICH MAY OR MAY NOT BE PASSED IN::
  
                           AND (@CampaignStartDate IS NULL OR ISNULL(ISNULL(Sales.vContractDates.FirstWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate) &gt;= @CampaignStartDate)
                           AND (@CampaignEndate IS NULL OR ISNULL(ISNULL(Sales.vContractDates.LastInstallWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate) &lt;= @CampaignEndate)
  
                            -- ONLY ONE STORE CAN BE SELECTED AT A TIME
  
                           AND 
  
                           (@isPNPSelected IS NULL OR @isPNPSelected = 1 OR Sales.Burst.ChainName LIKE N'%pick ''n pay%')
                           OR  (@isSparSelected IS NULL OR @isSparSelected = 1 OR Sales.Burst.ChainName LIKE N'%Spar%')
                           OR  (@isClicksSelected IS NULL OR @isClicksSelected =1 OR Sales.Burst.ChainName LIKE N'%Clicks%')
  
                           AND 
  
                           -- FILTER BY 1.) Chain. 2)Media Type. 3) ClientName. 4) BrandName. 5) ContractNumber
  
                           (@SearchText IS NULL OR @SearchText = '' 
                           OR Sales.AccountManager.FirstName LIKE N'%' + @SearchText + '%'
                           OR Sales.AccountManager.LastName LIKE N'%' + @SearchText + '%'
                           OR Sales.Burst.ChainName LIKE N'%' + @SearchText + '%'
                           OR Sales.Burst.MediaName LIKE N'%' + @SearchText + '%'
                           OR Client.Client.ClientName LIKE N'%' + @SearchText + '%' 
                           OR Sales.Burst.BrandName LIKE N'%' + @SearchText + '%' 
                           OR Sales.Contract.ContractNumber LIKE N'%' + @SearchText + '%')
  
  GROUP BY Sales.AccountManager.FirstName + N' ' + Sales.AccountManager.LastName, 
           Sales.Contract.ContractNumber, 
           Sales.AccountManager.Code, 
           Client.Client.ClientName, 
           DATEDIFF(wk, Sales.vContractDates.FirstWeek,Sales.vContractDates.LastInstallWeek) + 1, 
           ISNULL(ISNULL(Sales.vContractDates.FirstWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate), 
           ISNULL(ISNULL(Sales.vContractDates.LastInstallWeek,Sales.Contract.SignDate), Sales.Contract.CreationDate), 
           DATEADD(wk, 1, ISNULL(ISNULL(Sales.vContractDates.LastInstallWeek, Sales.Contract.SignDate), Sales.Contract.CreationDate)), 
           Sales.Contract.CreatedBy, 
           Sales.Contract.CreationDate, 
           Sales.Contract.SignedBy, 
           Sales.Contract.SignDate, 
           Sales.Contract.Signed, 
           Sales.Contract.ContractID,
        -- SELECTED FOR SCANNER DATA
        CAST(COALESCE([PriorYear_isSelectedForScannerData], [PriorPeriod_isSelectedForScannerData], [PostPeriod_isSelectedForScannerData])AS BIT),
        CAST(COALESCE([PriorYear_SelectedForScannerData_Date], [PriorPeriod_SelectedForScannerData_Date], [PostPeriod_SelectedForScannerData_Date])AS DATETIME),
       -- PNP READY
        CAST(COALESCE([PriorYear_isPNPDataReadyForPRC], [PriorPeriod_isPNPDataReadyForPRC], [PostPeriod_isPNPDataReadyForPRC])AS BIT),  
        CAST(COALESCE([PriorYear_PNPReadyDate], [PriorPeriod_PNPReadyDate], [PostPeriod_PNPReadyDate])AS DATETIME),
        CAST(COALESCE([PriorYear_CLICKSReadyDate], [PriorPeriod_CLICKSReadyDate], [PostPeriod_CLICKSReadyDate])AS DATETIME),
        CAST(COALESCE([PriorYear_SPARReadyDate], [PriorPeriod_SPARReadyDate], [PostPeriod_SPARReadyDate])AS DATETIME),
       -- PNP SUBMISSION
        CAST(COALESCE([PriorYear_isPNPDataSubmittedForPRC], [PriorPeriod_isPNPDataSubmittedForPRC], [PostPeriod_isPNPDataSubmittedForPRC])AS BIT), 
        CAST(COALESCE([PriorYear_PNPSubmissionDate], [PriorPeriod_PNPSubmissionDate], [PostPeriod_PNPSubmissionDate])AS DATETIME),
         -- CLICKS READY
        CAST(COALESCE([PriorYear_isCLICKSDataReadyForPRC], [PriorPeriod_isCLICKSDataReadyForPRC], [PostPeriod_isCLICKSDataReadyForPRC])AS BIT),  
       -- CLICKS SUBMISSION
        CAST(COALESCE([PriorYear_isCLICKSDataSubmittedForPRC], [PriorPeriod_isCLICKSDataSubmittedForPRC], [PostPeriod_isCLICKSDataSubmittedForPRC])AS BIT), 
        CAST(COALESCE([PriorYear_CLICKSSubmissionDate], [PriorPeriod_CLICKSSubmissionDate], [PostPeriod_CLICKSSubmissionDate])AS DATETIME),
        -- SPAR READY
        CAST(COALESCE([PriorYear_isSPARDataReadyForPRC], [PriorPeriod_isSPARDataReadyForPRC], [PostPeriod_isSPARDataReadyForPRC])AS BIT),  
       -- SPAR SUBMISSION
        CAST(COALESCE([PriorYear_isSPARDataSubmittedForPRC], [PriorPeriod_isSPARDataSubmittedForPRC], [PostPeriod_isSPARDataSubmittedForPRC])AS BIT), 
        CAST(COALESCE([PriorYear_SPARSubmissionDate], [PriorPeriod_SPARSubmissionDate], [PostPeriod_SPARSubmissionDate])AS DATETIME)
        return
  END
  </code></pre>
  
  <p>I created a LINQ To SQL dbml and dragged the stored procedure onto it and this is how I am calling it:</p>
  
  <pre><code>using (var db = new Contexts.Connections().ScannerDataToolDB())
  {
      var allContractsList = db.proc_AllContracts_Sel_Grid_Filter(SearchText, CampaignStartDate, CampaignEndate, isPNPSelected, isSparSelected, isClicksSelected).ToList();
  }
  </code></pre>
  
  <p>There are three problems with the stored procedure:</p>
  
  <ol>
  <li><p>I tested the <code>@SearchText</code> parameter (passing null to all the other parameters) and it basically does nothing. In other words, it has no effect - whether I leave it null or give it values that correlate to the data in the columns, it returns the same results (all the columns).</p>
  
  <p>Please help me fix this guys. I couldn't figure out what the problem was. I was trying to write 'safe' dynamic SQL.</p></li>
  <li><p>The second problem that I run into is that the C# code doesn't break but it returns no values. 0 Results. I know that the problem has be with the way that the query is written because I tested if my dbml works with other stored procedures (and they retrieved data just fine), including changing the body of this proc to return something like:</p>
  
  <pre><code>Select * Sales.Contract.ContractNumber
  </code></pre>
  
  <p>and it returned +800 000 records.</p></li>
  </ol>
  
  <p>I tested the other parameters and they don't seem to have any effect whatsoever.</p>
  
  <pre><code>EXEC    [Scanner].[proc_AllContracts_Sel_Grid_Filter]
          @SearchText = '',
          @CampaignStartDate =  @srtDate,
          @CampaignEndate = @endDate,
          @isPNPSelected = 1,
          @isSparSelected = 1,
          @isClicksSelected = 1
  </code></pre>
  
  <p>The query even generated the following warning message:</p>
  
  <blockquote>
    <p>Warning: Null value is eliminated by an aggregate or other SET operation.</p>
  </blockquote>
  
  <p>What am I doing horribly wrong? Please help me.</p>