sql-server-2008sql-server-2005linq-to-sqllinq-to-entitiesdbml

Handling Datatype errors in dbml


am getting an Unknown return type error When I am adding a stored procedure into my dbml . Below is my Sp. I am not able to figure out the error. I have checked too many questions related to it and the solution was to not use temp table.yes, I am not using temp table but using table variable as suggested by some blogs but then also I am getting error. Please help.

ALTER PROCEDURE dbo.Reports
    (
      @startDate datetime,
      @endDate datetime,
      @pageType int,
      @assignedClincian int
    )

AS

BEGIN

 Declare @SqlQuery varchar(max)    
 Declare @strCondition varchar(max)
 Declare @tempTable Table
 (
     First_Name varchar(150),
     Last_Name varchar(150),
     ID int,
     PageType_Id int,
     Rf_Date datetime,
     DOB datetime,
     InsuranceCompany varchar(250),
     AssignedClincian varchar(250)
 )


 set @SqlQuery =  'select * from tblusers'



        If @startDate IS NOT NULL AND @endDate is Not null
        Begin    
           set @strCondition = ' FO.Rf_Date  >= convert(datetime, ''' + Convert(varchar,@startDate,112) + ''') and  FO.Rf_Date<= convert(datetime, ''' + Convert(varchar,@endDate,112) + ''')'
        End  

        Else if @startDate IS NOT NULL 
        Begin
           set @strCondition = ' FO.Rf_Date  >=' + @startDate 
        End

        Else if @EndDate IS NOT NULL 
       Begin
          set @strCondition = ' FO.Rf_Date  <=' + @EndDate 
        End



      if @pageType > 0
      Begin
      set @strCondition = (case when @strCondition='' then ''  else @strCondition +  ' and ' end)  + ' FO.PageType_Id='+ Convert(varchar,@pageType)
      End

    if @assignedClincian > 0
      Begin
          set @strCondition = (case when @strCondition='' then ''  else @strCondition +  ' and ' end) +'  p1.AST_Clinician_Assginments_To=' + Convert(varchar,@assignedClincian)
      End



     set @SqlQuery=(case when @strCondition='' then @SqlQuery  else  @SqlQuery + ' where ' + @strCondition end) + ' order by Fo.ID'

         insert into @tempTable  EXEC (@SqlQuery)    

          select * from @tempTable 

END

RETURN

Solution

  • So, after huge mess, I came to end with a solution that in the second elseif condition, I was not converting date properly as in first if condition:

     Else if @startDate IS NOT NULL 
            Begin
               set @strCondition = ' FO.Rf_Date  >=' + @startDate 
            End