Select statements included within a function cannot return data to a client

[ryoka012]

Hi,

Can anyone help..
I iam creating a UDF Script in SQL 2005.

But i have a mesaage saying "Select statements included within a function cannot return data to a client"

Here is my whole code..

set @SubjectString = 'The quick brown fox JuMp over the lazy dog.'
set @CategoryString= 'jump' 

CREATE FUNCTION [dbo].[WordGet] 
(  @SubjectString VARCHAR(4000)
,@CategoryString varchar(100) )  

Returns Varchar(50)
AS
BEGIN
    Declare @Subject As varchar(100)
    Declare @Category As varchar(100)
IF ( @SubjectString LIKE '%'+@CategoryString+'%')
    SELECT SUBSTRING ( REPLACE (@SubjectString, LEFT(@SubjectString, CHARINDEX(@CategoryString, @SubjectString)-1), ''), 1, LEN(@CategoryString))   
    RETURN @CategoryString

   END
GO

Thanks.

[ss125]

I think the problem is with the statement

Returns Varchar(50)

or may be

RETURN @CategoryString

because the select statement itself will return the result. I am not sure. Anyway a try will never going to return a loss.

Have a happie day... :D

[1stDAN]

Could your select statement be incomplete? Usually this type of select needs an assignment, for example:

Declare @returnValueOfSelect As varchar(100)
set @returnValueOfSelect = ( SELECT SUBSTRING ( REPLACE 
(@SubjectString, LEFT(@SubjectString, CHARINDEX(@CategoryString,
@SubjectString)-1), ''), 1, LEN(@CategoryString)) )