problem with functions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

problem with functions

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-19 : 14:09:07

Afternoon,

This cannot be this difficult. All I want is a function to return data from a table. I can't used a table function because I need to pass a field value as a parameter. I tried just a plain vanilla function and I get the message

Msg 444, Level 16, State 2, Procedure fn_GetJobEndDate, Line 18
Select statements included within a function cannot return data to a client.

What a screaming pain. This is the plain function...the one that returns the error:


CREATE FUNCTION fn_GetJobEndDate (@custPO varchar(20))
RETURNS varchar
AS
BEGIN

	DECLARE @EndDate datetime;
	select top 1 Job_Operation.Sched_End
	from Job_Operation 
		inner join Job
			on Job_Operation.Job = Job.Job
	where Job_Operation.Sched_End is not null
    and Job.Customer_PO  = @custPO
    order by Job_Operation desc
	RETURN @EndDate

END
GO

Can someone please tell me how to go about fixing this? I need this in a select statement so I can't use a stored proc.

The select statement would be something like:


select fn_GetWorkCenter(Customer_PO)) as Current_WorkCntr,
fn_GetJobEndDate(Customer_PO))as  scheduled_end_date
from Backlog
where Sales_Order = '200763'

Thanks for any help you can provide.

Laura

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-19 : 15:03:44

[code]CREATE FUNCTION fn_GetJobEndDate (@custPO varchar(20))
RETURNS varchar
AS
BEGIN

DECLARE @EndDate datetime;
select top 1 @EndDate = Job_Operation.Sched_End
from Job_Operation
inner join Job
on Job_Operation.Job = Job.Job
where Job_Operation.Sched_End is not null
and Job.Customer_PO = @custPO
order by Job_Operation desc
RETURN @EndDate

END
GO[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-19 : 15:04:22

Or even better

CREATE FUNCTION fn_GetJobEndDate (@custPO varchar(20))
RETURNS varchar
AS
BEGIN
RETURN(
	select top 1 Job_Operation.Sched_End
	from Job_Operation 
		inner join Job
			on Job_Operation.Job = Job.Job
	where Job_Operation.Sched_End is not null
    and Job.Customer_PO  = @custPO
    order by Job_Operation desc
)
END
GO

N 56°04'39.26"
E 12°55'05.63"

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-07-19 : 15:06:47

Thanks so much I will try that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources