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
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