"select" statement in a function

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999
Begin
select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN
End
ELSE
if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
Begin
select MIN(lngPersonID) AS PersonID from Persons
where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName
and strMiddleName=@.MiddleName and strLastName=@.LastName
and strNameSuffix=@.NameSuffix and lngSSN=@.SSN
End
ELSE
if @.LastName is not null
BEGIN
select MIN(lngPersonID) as PersonID from Persons
where strLastName=@.LastName
and lngSSN = @.SSN
END
END
return (@.personID)
END

I m having problem with the "Select" function
the error I m getting is
Select statements included within a function cannot return data to a client (error 444)

Cann I use "select" statement in the function? If not what is the alternative?

I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.

ThanksWhy isn't this a stored procedure?|||Brett

It can be, but I guess you answered many of my questions
I still have this question left in me.

Can a stored procedure return a value??
As far as my knowledge goes it returns with a
"select" statement. Is it true?? Is there any way to check the returned value.
I am very much confused with this concept.

Can I make all functions in a sybase database as stored procedures in SQL??

If it doesnot return then I have to make it a function for sure.

Can you please answer these questions?

Thanks|||USE Northwind
GO

CREATE PROC MySproc99 @.HoursToMargarittaville Decimal(15,4) OUTPUT
AS
BEGIN
DECLARE @.Weekend datetime
SELECT @.Weekend = 'Jan 22 2004 17:00:00'
SELECT @.HoursToMargarittaville = DATEDIFF(mi, GetDate(), @.Weekend)/60.00
Return 0
END
GO

DECLARE @.x decimal(15,4), @.rc int
EXEC @.rc = MySproc99 @.x OUTPUT
SELECT @.x, @.rc
GO

DROP PROC mySproc99
GO|||You can use a select function for assignment, but you cannot return the results of a select statement.

Here is your function rewritten. I also cleaned up some of the unnecessary BEGIN/END pairs and used a COALESCE function rather than mutiple IS NULL checks.

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF COALESCE(@.LastName, @.FirstName, @.MiddleName, @.NamePrefix, @.NameSuffix) is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END

Be aware that I think your logic will fail when comparing NULL values, because NULL values cannot be compared to eachother. For example:
-----------
declare @.NULL1 varchar
declare @.NULL2 varchar
if @.NULL1 = @.NULL2 print 'Success' else print 'Failure'
-----------|||Oops. Looked at your code again and I see that you don't allow nulls for the comparison, so COALESCE is not appropriate. Use this instead:

CREATE FUNCTION GetPerson (@.SSN integer, @.NamePrefix varchar(10), @.FirstName varchar(30), @.MiddleName varchar(30), @.LastName varchar(40), @.NameSuffix varchar(10), @.HomeID integer, @.MailID integer, @.DOB timestamp, @.Gender varchar(1), @.MaritalStatus varchar(1))
RETURNS integer as
BEGIN
DECLARE @.PersonID integer
set @.PersonID=0
if @.SSN>0 and @.SSN<999999999 set @.PersonID = (select Min(lngPersonID) AS PersonID from Persons where lngSSN=@.SSN)
ELSE if @.SSN is not null
BEGIN
IF @.LastName is not null and @.FirstName is not null and @.MiddleName is not null and @.NamePrefix is not null and @.NameSuffix is not null
set @.PersonID = (select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @.NamePrefix and strFirstName=@.FirstName and strMiddleName=@.MiddleName and strLastName=@.LastName and strNameSuffix=@.NameSuffix and lngSSN=@.SSN)
ELSE if @.LastName is not null set @.PersonID = (select MIN(lngPersonID) as PersonID from Persons where strLastName=@.LastName and lngSSN = @.SSN)
END
return (@.personID)
END|||Yo, blind dude...

You don't think s/he would be better of with a sproc?

especially in light of a (sounds like massive) sybase to sql server conversion?|||Honestly, (MOO of course) I would use a function here, because I imagine this logic could be called by several different procedures. I've never liked using stored procedures that return scalar OUTPUT. I think the implementation is clunky, so since UDFs were introduced they have been my preferred method.|||MOO indeed...

Want to show me what the udf call would look like?|||see if you can not re-code your function to something like

Create function name (@.param...)
Returns Table

AS

Return Seturn Select ... Your SQL Goes here...

See also BOL, In-Line Functions

The only limitation is that you can have a single select sql phrase inside your function.|||I would call the UDF like this: MOO! MOOOOO! MOO MOO MOO MOO MOO!

Whether it would come when I call, I don't know...