帮助选择一个值到一个变量中；但是，列名也是一个变量. [英] Help with selecting a value into a variable; but, the column name is also a variable.

问题描述

我有一个视图，该视图调用一个进行计算的函数(输入日期)，并且一切正常.但是，需求已更改，输入日期值的列现在存储在另一张表中(而之前它在一个表中，我可以将其传递给函数).我的挑战是，我需要从其他表tbl_LURecordCode
中获取列名
vw_FolderRetentionDates是连接具有5到10个日期列的表的视图，这些日期列包含需要返回的值.根据tbl_LURecordCode中的赋值，我需要获取该值并将其传递给执行计算的现有函数.

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

此函数始终返回NULL，并告诉我Select @Result = @ColumnName试图将NULL分配给@Result，这是Date类型，导致NULL.

我能够使用动态SQL创建存储过程.但是，我无法从视图或函数中调用SP，而需要从视图中调用计算函数.

有另一种方法可以执行此操作，还是有任何方法可以使该功能在不使用动态SQL的情况下工作(我认为不存在；但是，我想我会把它扔掉，看看是否有人有想法)?

一种可能的解决方案是基于@ColumnName创建CASE语句并执行SQL:

伪代码:

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

唯一的缺点是，如果添加了另一个日期列，那么我们必须去更新此功能.

谢谢，
Pete

The only drawback is if another date column is added then we have to go and update this function.

Thanks,
Pete

推荐答案

我最终做到了，它的工作原理是:

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

您尝试过[@ColumnName].在这种情况下，将var视为列

have you tried [@ColumnName]. In that case a var is treated as column

这篇关于帮助选择一个值到一个变量中；但是，列名也是一个变量.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！