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

"Converting numbers into Dates"

I'm working in Access 2000. I was given some tables with number (long integer) data types for dates, such as 20041011 for Oct. 11, 2004. How can I convert these rows of numbers into dates? in order to re-format the table to accept them as dates?

I prefer to do this within the sql statement (update query, etc). What is the sql syntax to do this? (assume the column name is Notdate). Thanks!!!For Access you can do it like this

UPDATE Tablename
SET [NotDate] = DateSerial(Int(Left([notDate],4)),Int(Left(Right([NotDate],4),2)),INT(Right([NotDate],2)));|||It seems a better idea to me to create a second column D with datatype Date to store the result in:

UPDATE tablename SET D = DateSerial(...