How to assign a return value from a SQL Server Function to a Variable

Question

I have function that returns a Date value. I need to assign that return value to a declared variable.

Declare @Duedate Date
Set @Duedate = SELECT dbo.TATDueDateCaluator('2019-05-10',2)
Select @Duedate

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SELECT'.

sticky bit · Accepted Answer · 2019-05-29 12:27:30Z

2

If the function is scalar valued you don't even need a SELECT. Just

SET @duedate = dbo.tatduedatecaluator('2019-05-10', 2);

should do.

answered May 29, 2019 at 12:27

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

1 Comment

Sanjith M Over a year ago

Thanks for the answer. ;)

Gordon Linoff · Accepted Answer · 2019-05-29 12:25:34Z

2

You can assign directly in a SELECT:

Declare @Duedate Date;

SELECT @DueDate = dbo.TATDueDateCaluator('2019-05-10', 2);

Select @Duedate;

Your code doesn't work because subqueries always need their own parentheses.

edited May 29, 2019 at 12:25

answered May 29, 2019 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Sanjith M Over a year ago

Thanks for the answer. I have doubt is this Set the value to the variable or not? Because we usually to assign a value by using SET Variable_name = Value. Right?

Gordon Linoff Over a year ago

@SanjithM . . . Yes, this sets the variable.

Sanjith M Over a year ago

Thanks @Gordon Linoff.

nealkernohan · Accepted Answer · 2019-05-29 14:48:02Z

0

You can simply define it in one line:

Declare @Duedate Date = dbo.TATDueDateCaluator('2019-05-10',2)

Select Duedate

answered May 29, 2019 at 14:48

nealkernohan

8361 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to assign a return value from a SQL Server Function to a Variable

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related