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 helpThis 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|||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> ), ...|||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|||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 ), ...