I have a view that calls a function (input a date) that does a calculation and everything works fine; however, the requirements have change and the column for the input date value is now stored in another table (whereas before it was in one table and I could pass it into the function). My challenge is, I need to fetch the column name from the other table
is a view that joins tables that has 5 to 10 date columns that contains the value needed to be returned. Based on the assignment in
I need to fetch that value and pass it into the existing function that does the calculations.
Create Function [dbo].[fn_GetRetentionDate]
-- Declare the return variable here
DECLARE @Result as Date, @ColumnName as varchar(50)
-- Fetch the column assigned to the record code.
Select @ColumnName = RetentionDateColumnName From tbl_LURecordCode Where RecordCode = @recodeCode;
-- default to EndDate if not set.
IF @ColumnName IS NULL
set @ColumnName = 'EndDate';
-- fetch the value of the column name from the view
Select @Result = @ColumnName FROM vw_FolderRetentionDates Where Tracking_ID = @trackingID
This function always returns
and that tells me
Select @Result = @ColumnName
is trying to assign a
type resulting in
I was able to create a Stored Procedure using dynamic SQL; however, I can't call a SP from a View or a Function and I need to call the calculating function from the view.
Is there another way to do this or is there any way to get this to work in a function without using dynamic SQL (I don't think there is; but, I thought I would throw this out and see if anyone has an idea)?
A possible solution would be to create a
statement based on the
and execute the SQL:
Select @Result = EndDate FROM vw_FolderRetentionDates Where Tracking_ID = @trackingID
Select @Result = OtherDate FROM vw_FolderRetentionDates Where Tracking_ID = @trackingID
The only drawback is if another date column is added then we have to go and update this function.