none
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
    Tuesday, September 14, 2010 8:32 PM

Answers

  • 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:01 PM

All replies

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

    Tuesday, September 14, 2010 8:53 PM
  • 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:01 PM
  • thank you Kent! that helps a lot.
    JCD
    Tuesday, September 14, 2010 9:16 PM