在SQL2008中将变量传递给标量函数

I have a scalar function that looks up the current date (based on GETDATE()) in a table and returns a single value. The function accept three parameters: {MonthOffset}, {return_field}, and {CompanyId}. The {return field} is a VARCHAR(2) value that specifies which column value is to be returned - i.e. 'E' for ending_date, 'B' for beginning_date, ...

我有一个标量函数,它在表中查找当前日期(基于GETDATE())并返回单个值。该函数接受三个参数:{MonthOffset},{return_field}和{CompanyId}。 {return field}是一个VARCHAR(2)值,用于指定要返回的列值 - 即,对于ending_date为'E',对于beginning_date为'B',...

If I call the function as:

如果我将该函数称为:

SELECT [dbo].[HCS_fn_FiscalPeriodYear](-1, 'B', 'ABCDEF')

I get a proper return of the beginning_date.

我得到了一个正确的begin_date返回。

If I call the function as :

如果我将该函数称为:

DECLARE @CompanyID VARCHAR(12)
SET @CompanyID = 'ABCDEF'
SELECT [dbo].[HCS_fn_FiscalPeriodYear](-1, 'B', '@CompanyID')

I get a return value from the function of 0 that is the error trap value in the function.

我从函数0得到一个返回值,它是函数中的错误陷阱值。

Why am I NOT able to pass the variable value to the function and get a proper return? Thanks in advance...

为什么我无法将变量值传递给函数并获得正确的返回值?提前致谢...

2 个解决方案

#1

It looks like you have an issue with the variable being placed in single quotes so that the function is really getting a string of '@CompanyID' instead of 'ABCDEF'.

看起来你有一个问题,变量被放在单引号中,这样函数实际上得到一个'@CompanyID'字符串而不是'ABCDEF'。

#2

Putting something in single quotes makes it a string. This is how you should pass your parameter

把东西放在单引号中使它成为一个字符串。这是您应该传递参数的方式

DECLARE @CompanyID VARCHAR(12)
SET @CompanyID = 'ABCDEF'

SELECT [dbo].[HCS_fn_FiscalPeriodYear](-1, 'B', @CompanyID)

#1