Error: "Select statements included within a function cannot return data to a client"

Question

hi all,

Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error:
"Select statements included within a function cannot return data to a client"

All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement.
If the date IS NOT null then run a different SELECT statement.

CREATE FUNCTION [SchemaTest].[fn_Test]
(
	 @instance as uniqueidentifier
	,@date as datetime = null
)
RETURNS varchar(8000)
AS
BEGIN
	If (@date is null) --@date = null ??
	Begin
		Select *
		From Uds.RealtimeLogging
		Where instanceID = @instance
	End

	Else
	Begin
		Select *
		From Uds.RealtimeLogging
		Where instanceID = @instance and [timeStamp] > @date
	End

	Return @stepIds
END

Thank you!

---

JCD

Answer 1

There is a list of constraints that are placed on functions that are not placed on stored procedures.  Please read the CREATE FUNCTION article in books online.  Also, if you must return data such as this you might want to consider converting your function into a stored procedure.

EDIT:

In this case it might be better to consider converting your function into a table function.

Answer 2

The syntax for an inline table function might look something like:

create function [SchemaTest].[fn_Test]
( @instance as uniqueIdentifier,
 @date as datetime = null)
returns table
as
return
( select <list the fields> -- don't use the '*' notation
 from uds.RealTimeLogging
 where @instanceId = @instance
  and (@date is null or [timestamp] > @date )
)
-- ----- This code is not optimized -- 
-- ----- If you need this optimized, someone will surely step
-- ----- up in here and help you

Answer 3

thank you Kent! that helps a lot.

---

JCD