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

Do both in one statement, e.g.

select @ReturnValue = cast(coalesce(TotalCardsAssigned,0) as varchar(2) + ' of ' + cast(coalesce(TotalProcsAssigned,0) as varchar(2)) 
from (select TotalProcAssigned = count(*),
TotalCardsAssigned = case when CardAssigned>0 then 1 else 0 end from dbo.meetproc where meetingnumber=@tiMeetID) X

this way you don't need variables at all.

Or

;with cte_CountsInfo as (select TotalProcAssigned = count(*),
TotalCardsAssigned = case when CardAssigned>0 then 1 else 0 end from dbo.meetproc where meetingnumber=@tiMeetID)

select @ReturnValue = cast(coalesce(TotalCardsAssigned,0) as varchar(2) + ' of ' + cast(coalesce(TotalProcsAssigned,0) as varchar(2)) 
from  cte_CountsInfo