>I'm wondering if there is a better way to write this function? I'm populating two variables which I then use in the returnvalue. Basically there could 1 or many records for each meetingnumber in the meetproc table. There is a int field in the meetproc table that stores a card number if assigned or a 0 if no card has been assigned. This function is getting called from the main stored procedure, so for each record returned in the main query, this function is calling two more queries. It works correctly and returns valid data, I'm just in the process of trying to optimize for a big load. Thanks in advance.
>
>

>ALTER FUNCTION [dbo].[Get_CardsAssignedNumeric]  (@tiMeetID int)  
>RETURNS varchar(10) AS  
>BEGIN 
>     Declare @returnValue varchar(10)
>     set @returnValue=' '
>     declare @TotalProcsAssigned int
>     declare @TotalCardsAssigned int 
>     set @TotalProcsAssigned = (Select count(*) from dbo.meetproc where meetingnumber=@tiMeetID)
>     set @TotalCardsAssigned = (Select count(*) from dbo.meetproc where meetingnumber=@tiMeetID and cardassigned > 0)
>     set @ReturnValue=cast(@TotalCardsAssigned as varchar(2))+' of '+cast(@TotalProcsAssigned as varchar(2))
>
>     return (@returnValue)
>END
>

You could do it as a TVF so you wouldn't need any variables and return the result as a 'table'. ie:

Create FUNCTION [dbo].[Get_CardsAssignedNumeric]  (@tiMeetID int)  
RETURNS table AS  
BEGIN 
select 
         TotalProcsAssigned, 
         TotalCardsAssigned, 
         cast(TotalCardsAssigned as varchar(2))+' of '+cast(TotalProcsAssigned as varchar(2)) as ReturnValue
FROM 
  (Select count(*) as TotalProcsAssigned, 
             sum( when cardassigned > 0 then 1 else 0 end ) as TotalCardsAssigned
             from dbo.meetproc 
             where meetingnumber=@tiMeetID) tmp
END

Sample use:

select * from [dbo].[Get_CardsAssignedNumeric](1)

Cetin