Results 1 to 5 of 5

Thread: function

  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Unanswered: function

    Hello
    I use ms sql2000
    If i would like to do a function
    that gives me a value of "*"
    if a specific field is empty
    how will i do please help

  2. #2
    Join Date
    Feb 2003
    Posts
    8

    function

    This is how it looks


    two tables
    Activity
    Actparttime



    i try whith this function

    CREATE FUNCTION dbo.Actact (@ID AS int)
    RETURNS varchar(1)
    AS
    BEGIN
    DECLARE @Aact AS varchar(1)
    SELECT DISTINCT @Aact = "*" WHERE (SELECT dbo.Activity.ID
    FROM dbo.Activity INNER JOIN
    dbo.Actparttime ON dbo.Activity.ID = dbo.Actparttime.Actid
    WHERE (dbo.Actparttime.Actpartend IS NULL) AND (dbo.Activity.ID = @ID)) >""


    RETURN @Aact
    END

    and a view

    SELECT ID, ActStart, ActEnd, Activity, dbo.Actact(ID) AS Aact
    FROM dbo.Activity


    i get that subquery returned more than 1 reply




    please help

  3. #3
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    another way to check:

    CREATE FUNCTION dbo.IsEmpty ( @COL sql_variant )
    RETURNS varchar(1)
    AS
    BEGIN
    DECLARE @Ret AS varchar(1)
    SELECT @Ret = case when convert(varchar,IsNull(@COL,''))='' then '*' else '' end
    RETURN @Ret
    END
    go


    select dbo.sEmpty( <ColumnName> ), ...

  4. #4
    Join Date
    Feb 2003
    Posts
    8

    function

    Thanks

    But how do i do when i will have
    it sorted on activity table
    If there is more than one field i get an error for
    many rows

  5. #5
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: function

    Originally posted by u31115057
    Thanks

    But how do i do when i will have
    it sorted on activity table
    If there is more than one field i get an error for
    many rows

    something like this ?

    CREATE FUNCTION dbo.IsEmpty (
    @COL1 sql_variant ,
    @COL2 sql_variant ,
    @COL3 sql_variant ,
    @COL4 sql_variant ,
    @COL5 sql_variant
    ) RETURNS varchar(1)
    AS BEGIN
    DECLARE @Ret AS varchar(1)
    SELECT @Ret = case when
    convert(varchar,IsNull(@COL1,''))+
    convert(varchar,IsNull(@COL2,''))+
    convert(varchar,IsNull(@COL3,''))+
    convert(varchar,IsNull(@COL4,''))+
    convert(varchar,IsNull(@COL5,''))
    ='' then '*' else '' end
    RETURN @Ret
    END
    go

    AND

    select dbo.IsEmpty( <colname1>, <colname2>, null, null, null ), ...

Posting Permissions

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