Tuesday, September 14, 2010 8:32 PM
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.Thank you!
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
Tuesday, September 14, 2010 8:53 PMModerator
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.
In this case it might be better to consider converting your function into a table function.
Tuesday, September 14, 2010 9:01 PMModerator
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
- Marked As Answer by Maximusdm Tuesday, September 14, 2010 9:16 PM
Tuesday, September 14, 2010 9:16 PMthank you Kent! that helps a lot.