Click here to Skip to main content
14,599,112 members
Rate this:
Please Sign up or sign in to vote.
See more:
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 tbl_LURecordCode

vw_FolderRetentionDates 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 tbl_LURecordCode I need to fetch that value and pass it into the existing function that does the calculations.



Create Function [dbo].[fn_GetRetentionDate]
(
    @recodeCode varchar(10),
    @trackingID varchar(38)
)
Returns Date
AS
BEGIN
    -- 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
    
    Return @Result
END


This function always returns NULL and that tells me Select @Result = @ColumnName is trying to assign a NULL to @Result which is Date type resulting in NULL.

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 CASE statement based on the @ColumnName and execute the SQL:

pseudo-code:
Case 'EndDate'
   Select @Result = EndDate FROM vw_FolderRetentionDates Where Tracking_ID = @trackingID
Case 'OtherDate'
   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.

Thanks,
Pete
Posted
Updated 17-Sep-11 9:43am
v3
Comments
Mehdi Gholam 18-Sep-11 1:28am
   
Can't you use code outside of SQL to do this (it's less painful)?
Pete BSC 18-Sep-11 10:24am
   
@Mehdi Gholam: you are right it would be less painful; however, to make things more configurable this is done in the database to not have to deploy a new version. My hands are somewhat tied by the requirements.
Rate this:
Please Sign up or sign in to vote.

Solution 2

I ended up doing this and it works:
ALTER Function [dbo].[fn_GetRetentionDate]
(
	@recodeCode varchar(10),
	@trackingID varchar(38),
	@endDate date
)
Returns Date
AS
BEGIN
	-- 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
	Return Case @ColumnName
	 When 'SettleDate' Then (SELECT SettleDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'EndDate' Then @endDate --(SELECT EndDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'TerminationDate' Then (SELECT TerminationDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'TradeDate' Then (SELECT TradeDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'PaidDate' Then (SELECT PaidDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'InvoiceDate' Then (SELECT InvoiceDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)
	 When 'FundEndDate' Then (SELECT FundEndDate from vw_FolderRetentionDates Where Tracking_ID = @trackingID)		 ELSE NULL
	End	
	
END
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

have you tried [@ColumnName]. In that case a var is treated as column
   
Comments
Pete BSC 18-Sep-11 10:28am
   
I get an error: Incorrect syntax near ']'. Using SQL 2008R2 I get intellisense message: Invalid column name '@ColumnName'.

That was a good idea to try though!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100