I have two tables, Contacts, Accounts. I am trying to create a general function that will get the number of records associated with same parent, here is what I have:
CREATE FUNCTION [dbo].[SiblingCount](
RETURNS Integer AS
DECLARE @Result integer
Select @Result = Count(a.AccountID)
from Account a
where a.Status <> 'Active' and
a.ContactID = (select a2.ContactID
from Account a2 where a2.Accounnts = @IDValue)
Are you really talking about siblings? In your query you do not even touch the table
Contacts... I think, that you have a 1:n relation between Concats and Accounts and what you really need is a count of children...
I think this could be done much easier like this:
SELECT c.ContactID,COUNT(*) AS CountAccounts FROM Contacts AS c INNER JOIN Accounts AS a ON c.ContactID=a.ContactID WHERE a.Status<>'Active' GROUP BY c.ContactID
AND c.ContactID=@IDValue to the
WHERE to reduce the result to the one line you need...