Help with UDF - simple count query

[leveetated]

Hello experts,

I've got a UDF that should simply count the number of objects in a container. However, I cannot pass the container ID or any container info to it - only the ID of one object.

So I want the UDF to do this: Find the container it's currently in, AND do a count of ALL other objects currently in that container too. I have a view that contains the info I need.

Here's what I have so far but it simply returns the number of objects per object ID, not per container:

create function [dbo].[count_objsContainer](@objectid varchar(200))
returns varchar(64) as
begin
  declare @str varchar(64)
  select @str = count(componentid) 
  from objcurlocview where objectid = @objectid
  return @str
end

How can I *broaden* my count in the UDF to count all objects in the container, and not just the one object I'm passing to the UDF?

Many, many thanks in advance,

Lee.

 

[leveetated]

Apologies: I'm using MS SQLServer 2005.

 

[gmmastros]

So I want the UDF to do this: [!]Find the container it's currently in[/!], AND do a count of ALL other objects currently in that container too. I have a view that contains the info I need.

Can you write a query that finds the container it's currently in? Can you show us that query?


By the way.... why does your function return a varchar(64) instead of an integer?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[leveetated]

The view that I'm referencing already has the current container it's in, so I can get that easily, along with all other objects and object ID's and their current container. It's counting up all the OTHER objects in the container that's causing me headaches...I know this must be so simple!

You're right - it should be an int! Will change that.

Many thanks, Lee.

 

[gmmastros]

I think it should be something like this:

create function [dbo].[count_objsContainer](@objectid varchar(200))
returns int as
begin
  Declare @ContainerId Int

  Select @ContainerId = Container	
  from   objcurlocview 
  where  objectid = @objectid

  return(Select Count(ComponentId) from objcurlocview where ContainerId = @ContainerId)
end

Since you didn't show the query I asked for, I had to guess about the column name and it's data type.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[leveetated]

Of course, I just had a little light bulb go off (why do I always figure things out promptly after I post a question..? sigh).

I made another view that did a count on each container in the other view, and grouped by container name. Then did a where clause in the view included in the previous post to match up container names.

Many thanks for your assistance and apologies for the false start. All the best,

Lee.