Passing a variable to a scalar function in SQL2008

Question

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

If I call the function as:

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

I get a proper return of the beginning_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.

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

Variables do not need to be encased in quotes.

Mr. Mascaro
– Mr. Mascaro

2015-05-06 14:24:14 +00:00
Commented May 6, 2015 at 14:24 — Mr. Mascaro
– Mr. Mascaro, Commented May 6, 2015 at 14:24

Scott · Accepted Answer · 2015-05-06 14:23:06Z

2

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

answered May 6, 2015 at 14:23

Scott

3702 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQL-GBH Over a year ago

The single quotes around the variable was exactly the problem. The quotes were an artifact from testing before using the variable. It is working fine now. Sometimes it's the most obvious that we can't see! Thanks to all.

Stephan · Accepted Answer · 2015-05-06 14:26:20Z

0

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)

answered May 6, 2015 at 14:26

Stephan

6,0481 gold badge19 silver badges26 bronze badges

Collectives™ on Stack Overflow

Passing a variable to a scalar function in SQL2008

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related