Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Posts
    40

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





    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why isn't this a stored procedure?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Posts
    40
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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'
    ----------------------------------
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    MOO indeed...

    Want to show me what the udf call would look like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •