How to create sproc with unlimited number of parameters?

We have this kind of thing in C#, and I know the sp_executesql (or something like that) does it too. How do you create a stored procedure that has certain known parameters, followed by any number of additional parameters?

For example, you may have one that's called in any of these ways:

exec my_sp @.ID, @.text, @.P1, @.P2

exec my_sp @.ID, @.text, @.P1, @.P2, @.P3

exec my_sp @.ID, @.text, @.P4, @.P39087, @.P2

I can't find a syntax in the docs that really accommodates this.

Thanks in advance for your help!

Is something like this what you are looking for? This defines a procedure with M required parameters and N optional parameters:

create procedure dbo.xample
( @.pm_required_01 <type#01>,
@.pm_required_02 <type#02>,
...
@.pm_required_M <type#M>,
@.pm_optional_01 <type#01> = <defaultValue#01>,
@.pm_optional_02 <type#02> = <defaultValue#02>,
...
@.pm_optional_N <Type#N> = <defaultValue#N>
)
as

yourCode

|||Does this solution enforce a maximum number of parameters though? If so, that's not quite what I was after.

I guess the other problem too is that there has to be some way to iterate through all of the parameters as well.|||

Jeff:

It sounds to me like you want to use a "list" as an argument to the stored procedure; if that is the case examine this article by Erland Sommarskog and see if this is the correct orientation:

http://www.sommarskog.se/arrays-in-sql.html

Also, there is a response here from Jens Suessmeyer that has some more about using a "string list":

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

TSQL has a hard limit of ~2K Parameters to any module. Thus, there is no way to do what your asking natively in TSQL (i.e, the best you can do is 2K sql_variant parameters with a default defined ).

People have used various techniques to get around this problem, most of which are nicely summarized in the links above.

How to create apps that write code to retrieve data with foreign keys?

Hi Guys

Off late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?

Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?

Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?

Thanks

You use the Pk-FK relation to join the two tables and retrieve the columns from either of the two tables.

SELECT t1.col1, t2,col2, t2.col3

FROM Table1 t1

JOIN Table2 t2 ON t1.somecolumn = t2.somecolumn

Assuming the 'SomeColumn' here is the common column between the two tables, the above SELECT statement can be modified to retrieve columns from either of the tables. And I dont think it is cumbersome to retrieve info from another table. Your tables have to be properly normalized. This is the key. Joining too many tables in the query could also be detrimental. It depends on how well your tables are normalized.

>> Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?
It may not be a good tatabase technique to bend normalcy rules but from a practical/real world perpspective, sometimes, people do have redundant data. If you have enough justification (not just laziness or saving time or writing less code) then yes.

>>Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?
Sure, I did that above already.

|||

Thanks ndinakar for the reply. Code that I referred here is not the SQL query, that is not the cause of concern as I too know to retrieve the data from another table which has the Pk-Fk relationship another table with an SQL query. However, it often requires quite a lot of C# or VB code to retrieve the data from the other table when relationships are used.

Can you tell me if there are any in-built code that could be used to retrieve the data from the Pk table given the Fk table? I think I was not clear in my post and this is now clear.

Thanks

sql

How to create an event driven shared schedule

We have a number of jobs that run nightly. Typically these complete long before the shared schedule runs. However, when they do not complete in time, we need to stop the shared schedule from running. How is this accomplished?

I have seen that it is possible to create event driven subscriptions but am not sure this accomplishes the same thing. Basically, I need to pause the shared schedule if a particular job has not completed successfully. Then, I need to run the shared schedule once the job has completed. None of the reports we are running are snapshot based. Would moving to this method resolve the issue? Any help is greatly appreciated.

I had the same issue. What I did was kick off the subscription SQL Agent job at the end of my ETL job. That way I know the import will always be done before the reports are published.

BobP

|||

That will have to be the solution we employ as well, at least until a better solution can be found.

Thanks!

How to create an admin with some limitations?

I've a SQL 2005 with a certain number of db.
Until today i was the only admin of all dbs.
Now i need to give to another admin permission to manage one specific
db (but only that).
Ho censito un utente di sql con accessi abilitati solo a quel db.
If i connect to Enterprise manager with that limited login there is a
problem: i see (only see)
all dbs, also db where this login is not autorized.
How can i work for not permitting this login to see other dbs? He
only
can see them but i
don't want because of privacy. Is it possible?(bertainafederico@.gmail.com) writes:

Quote:

Originally Posted by

I've a SQL 2005 with a certain number of db.
Until today i was the only admin of all dbs.
Now i need to give to another admin permission to manage one specific
db (but only that).
Ho censito un utente di sql con accessi abilitati solo a quel db.
If i connect to Enterprise manager with that limited login there is a
problem: i see (only see)
all dbs, also db where this login is not autorized.
How can i work for not permitting this login to see other dbs? He
only
can see them but i
don't want because of privacy. Is it possible?

Did you try DENY VIEW ANY DATABASE TO Tizio as I suggested in response to
your Italian post?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, i try and now i can work correctly with limited user onto the
database.
But in the listing of db i can't see any db... So now the problem is
making possible to the user
viewing his db|||My limited user now can only see master and tempdb in the list of
dbs...|||Ok, resolved reading an italian tutorial. Thanks of allsql

how to create a sequence invoice number and insert or update to a column?

Hi, can anyone teach me how to automatic create a invoice number and insert or update it to a column?search in your nearest textbook or search engine for "sql server IDENTITY column"|||C'mon Rudy. You know what is coming next:

"I want my invoice number to have a product code in it, and I want it to look up the last number used for each product code and increment it."

How to Create a Sequence Generator number in SSIS

Hi,

I got 5000 rows in source and when i am sending the data to destination it has to create a sequence generator number for each row.

Can any one help me which transformation do i need to take for doing this in SSIS.

Row Number Transformation
(http://www.sqlis.com/default.aspx?93)|||

Alternatively you can also look the following articles

Surrogate key generation in SSIS @. http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

Generating Surrogate Keys @. http://www.sqlis.com/default.aspx?37

Thanks,
Loonysan
http://mystutter.blogspot.com/

How to create a row that calucates percentage in a Tabular report, RS 2005?

Hello,

I have a report in a Tabular format and I am wondering if anyone knows how I can create a row that generates a percentage number in RS 2005. Sample data is highlited here in yellow?

Thanks,

School

Dept

Level

Category

Total Courses

Enrollment

Grades Awarded

Grades Attempted

Average GPA

A

A-

B+

B

B-

BUE

MGMT

300

Classroom Instruction

30

844

813

817

3.213

140

166

164

138

109

17%

20%

20%

17%

13%

400

Classroom Instruction

32

751

734

740

3.417

227

173

135

83

59

31%

24%

18%

11%

8%

Does the number under each grade come from a different database field? Assuming for the "A" column, the number is from FieldA, you can calculate the percentage (for number of "A"s for this level from total number of "A"s) using =Fields!FieldA.Value/Sum(Fields!FieldA.Value). For the percentage of the number of "A"s from total number for all grades for a specific level, use =Fields!FieldA.Value/(Fields!FieldA.Value+Fields!FieldA-.Value+Fields!FieldB+.Value+Fields!FieldB.Value+Fields!FieldB-.Value).

How to count up group number

Hi,
I have a report with a group and detail rows. I want to count up the groups
in the column groupnumber.
I tried the rownumber(<scope>) function.
If I use rownumber (Nothing) the detail rows were counted.
If I use rownumber("Groupname") the Report Server shows the number of detail
rows for each group.
Which scope should I use to count up the single groups?
Best regards,
StefanMaybe declare a global variable (i) in the Custom code and add this variable
in the value of a field in your group (i.e. =i+1)
"Stefoon23" wrote:
> Hi,
> I have a report with a group and detail rows. I want to count up the groups
> in the column groupnumber.
> I tried the rownumber(<scope>) function.
> If I use rownumber (Nothing) the detail rows were counted.
> If I use rownumber("Groupname") the Report Server shows the number of detail
> rows for each group.
> Which scope should I use to count up the single groups?
> Best regards,
> Stefan|||Hi Kyriakos,
your answer makes sense. But I couldn't get it to work!
How do I declare a custom global variable and how could I access it in the
report?
Best regards,
Stefan
"Kyriakos" wrote:
> Maybe declare a global variable (i) in the Custom code and add this variable
> in the value of a field in your group (i.e. =i+1)
> "Stefoon23" wrote:
> > Hi,
> >
> > I have a report with a group and detail rows. I want to count up the groups
> > in the column groupnumber.
> >
> > I tried the rownumber(<scope>) function.
> > If I use rownumber (Nothing) the detail rows were counted.
> > If I use rownumber("Groupname") the Report Server shows the number of detail
> > rows for each group.
> > Which scope should I use to count up the single groups?
> >
> > Best regards,
> > Stefan|||Add this code at your custom code section
============Public Shared i as integer
Public Function GetGrp() As Integer
i +=1
Return i
End Function
============
then call the function within a field on the group level, i.e. =Code.GetGrp()
That should do the count.
"Stefoon23" wrote:
> Hi Kyriakos,
> your answer makes sense. But I couldn't get it to work!
> How do I declare a custom global variable and how could I access it in the
> report?
> Best regards,
> Stefan
> "Kyriakos" wrote:
> > Maybe declare a global variable (i) in the Custom code and add this variable
> > in the value of a field in your group (i.e. =i+1)
> >
> > "Stefoon23" wrote:
> >
> > > Hi,
> > >
> > > I have a report with a group and detail rows. I want to count up the groups
> > > in the column groupnumber.
> > >
> > > I tried the rownumber(<scope>) function.
> > > If I use rownumber (Nothing) the detail rows were counted.
> > > If I use rownumber("Groupname") the Report Server shows the number of detail
> > > rows for each group.
> > > Which scope should I use to count up the single groups?
> > >
> > > Best regards,
> > > Stefan|||Hi Kyriakos, it worked perfectly!
Thanks for your help!
Stefoon
"Kyriakos" wrote:
> Add this code at your custom code section
> ============> Public Shared i as integer
> Public Function GetGrp() As Integer
> i +=1
> Return i
> End Function
> ============> then call the function within a field on the group level, i.e. =Code.GetGrp()
>
> That should do the count.
> "Stefoon23" wrote:
> > Hi Kyriakos,
> > your answer makes sense. But I couldn't get it to work!
> > How do I declare a custom global variable and how could I access it in the
> > report?
> >
> > Best regards,
> > Stefan
> >
> > "Kyriakos" wrote:
> >
> > > Maybe declare a global variable (i) in the Custom code and add this variable
> > > in the value of a field in your group (i.e. =i+1)
> > >
> > > "Stefoon23" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a report with a group and detail rows. I want to count up the groups
> > > > in the column groupnumber.
> > > >
> > > > I tried the rownumber(<scope>) function.
> > > > If I use rownumber (Nothing) the detail rows were counted.
> > > > If I use rownumber("Groupname") the Report Server shows the number of detail
> > > > rows for each group.
> > > > Which scope should I use to count up the single groups?
> > > >
> > > > Best regards,
> > > > Stefan|||Hi Kyriakos,
I wrote it works perfectly. It works when I Render the report the first
time. But when I choose other parameters the counting is not initialized.
Thus it does not start at zero but at the highest number.
e.g. I have a report with 5 group members for a group level the count would
go from 1 to 5 the first time I render the report. The second time the
numbers would reach from 6 to 10. ;-)
How could I initialize the value of the global variable every time the
report is rendered again? Or maybe it would be a solution to init the value
when the report has finished rendering.
Best regards,
Stefoon
"Kyriakos" wrote:
> Add this code at your custom code section
> ============> Public Shared i as integer
> Public Function GetGrp() As Integer
> i +=1
> Return i
> End Function
> ============> then call the function within a field on the group level, i.e. =Code.GetGrp()
>
> That should do the count.
> "Stefoon23" wrote:
> > Hi Kyriakos,
> > your answer makes sense. But I couldn't get it to work!
> > How do I declare a custom global variable and how could I access it in the
> > report?
> >
> > Best regards,
> > Stefan
> >
> > "Kyriakos" wrote:
> >
> > > Maybe declare a global variable (i) in the Custom code and add this variable
> > > in the value of a field in your group (i.e. =i+1)
> > >
> > > "Stefoon23" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a report with a group and detail rows. I want to count up the groups
> > > > in the column groupnumber.
> > > >
> > > > I tried the rownumber(<scope>) function.
> > > > If I use rownumber (Nothing) the detail rows were counted.
> > > > If I use rownumber("Groupname") the Report Server shows the number of detail
> > > > rows for each group.
> > > > Which scope should I use to count up the single groups?
> > > >
> > > > Best regards,
> > > > Stefan|||I could solve the puzzle by myself:
add some more custom code to your project
===================Public Shared i as integer = 0
Public Function GetGrp() As Integer
i +=1
Return i
End Function
Public Function InitGrp() As Integer
i=0
Return i
End Function
=======================
Put the Function InitGrp() to the page header.
Best regards,
Stefoon
"Stefoon23" wrote:
> Hi Kyriakos,
> I wrote it works perfectly. It works when I Render the report the first
> time. But when I choose other parameters the counting is not initialized.
> Thus it does not start at zero but at the highest number.
> e.g. I have a report with 5 group members for a group level the count would
> go from 1 to 5 the first time I render the report. The second time the
> numbers would reach from 6 to 10. ;-)
> How could I initialize the value of the global variable every time the
> report is rendered again? Or maybe it would be a solution to init the value
> when the report has finished rendering.
> Best regards,
> Stefoon
>
> "Kyriakos" wrote:
> > Add this code at your custom code section
> >
> > ============> > Public Shared i as integer
> >
> > Public Function GetGrp() As Integer
> > i +=1
> > Return i
> > End Function
> > ============> >
> > then call the function within a field on the group level, i.e. =Code.GetGrp()
> >
> >
> > That should do the count.
> >
> > "Stefoon23" wrote:
> >
> > > Hi Kyriakos,
> > > your answer makes sense. But I couldn't get it to work!
> > > How do I declare a custom global variable and how could I access it in the
> > > report?
> > >
> > > Best regards,
> > > Stefan
> > >
> > > "Kyriakos" wrote:
> > >
> > > > Maybe declare a global variable (i) in the Custom code and add this variable
> > > > in the value of a field in your group (i.e. =i+1)
> > > >
> > > > "Stefoon23" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a report with a group and detail rows. I want to count up the groups
> > > > > in the column groupnumber.
> > > > >
> > > > > I tried the rownumber(<scope>) function.
> > > > > If I use rownumber (Nothing) the detail rows were counted.
> > > > > If I use rownumber("Groupname") the Report Server shows the number of detail
> > > > > rows for each group.
> > > > > Which scope should I use to count up the single groups?
> > > > >
> > > > > Best regards,
> > > > > Stefan|||:-)
"Stefoon23" wrote:
> I could solve the puzzle by myself:
> add some more custom code to your project
> ===================> Public Shared i as integer = 0
> Public Function GetGrp() As Integer
> i +=1
> Return i
> End Function
>
> Public Function InitGrp() As Integer
> i=0
> Return i
> End Function
> =======================> Put the Function InitGrp() to the page header.
> Best regards,
> Stefoon
> "Stefoon23" wrote:
> > Hi Kyriakos,
> > I wrote it works perfectly. It works when I Render the report the first
> > time. But when I choose other parameters the counting is not initialized.
> > Thus it does not start at zero but at the highest number.
> > e.g. I have a report with 5 group members for a group level the count would
> > go from 1 to 5 the first time I render the report. The second time the
> > numbers would reach from 6 to 10. ;-)
> >
> > How could I initialize the value of the global variable every time the
> > report is rendered again? Or maybe it would be a solution to init the value
> > when the report has finished rendering.
> >
> > Best regards,
> > Stefoon
> >
> >
> >
> > "Kyriakos" wrote:
> >
> > > Add this code at your custom code section
> > >
> > > ============> > > Public Shared i as integer
> > >
> > > Public Function GetGrp() As Integer
> > > i +=1
> > > Return i
> > > End Function
> > > ============> > >
> > > then call the function within a field on the group level, i.e. =Code.GetGrp()
> > >
> > >
> > > That should do the count.
> > >
> > > "Stefoon23" wrote:
> > >
> > > > Hi Kyriakos,
> > > > your answer makes sense. But I couldn't get it to work!
> > > > How do I declare a custom global variable and how could I access it in the
> > > > report?
> > > >
> > > > Best regards,
> > > > Stefan
> > > >
> > > > "Kyriakos" wrote:
> > > >
> > > > > Maybe declare a global variable (i) in the Custom code and add this variable
> > > > > in the value of a field in your group (i.e. =i+1)
> > > > >
> > > > > "Stefoon23" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a report with a group and detail rows. I want to count up the groups
> > > > > > in the column groupnumber.
> > > > > >
> > > > > > I tried the rownumber(<scope>) function.
> > > > > > If I use rownumber (Nothing) the detail rows were counted.
> > > > > > If I use rownumber("Groupname") the Report Server shows the number of detail
> > > > > > rows for each group.
> > > > > > Which scope should I use to count up the single groups?
> > > > > >
> > > > > > Best regards,
> > > > > > Stefan

How to count tuples with specific value?

Hi, all

How can we count the number of tuples with specific value? (e.g, I want to get the number of tuples with value of 'measure_name'<600, how can we achieve this?)

Thank you and I am looking forward to hearing from you shortly.

With kind regards,

Yours sincerely,

Hi, experts here,

Please help me out!

Thanks.

With kind regards,

Yours sincerely,

|||

Hi there,

i've hab the same problem like you. I've solve this with a lookupcupe query.

1. Create a CalcMeasure in your Cube like

IIF (Measures.SalesOrders > 600, 1,0)

2. Do a MDX Query like

With

....

Member Measures.MySalesOrders as lookupcube("CUBENAME","SUM({Dimension.Members}, Measures.SalesOrders)")

3. Thats it. There is a different to query the Measure SalesOrders direct wihtout a Lookupcube. Try it with and without. It very important to use the SUM function. In other way it will aggregate all values.

Please try it out. I'm very pleased to hear from you.

Kind Regards

Andy

-

http://www.andyloewen.de

|||

Hi, Andy,

Thanks for your kind suggestions.

I have struggled ages to figure this out. But it is still not working.

Now I am going to try an alternative which is a named calculations within the data source view, and get the count for that named calculation member.

Thank you.

With kind regards,

Yours sincerely,

How to count the number of textboxes

Hi all,

In the report I am working on, I have a "textbox39" in a table which has groups. I want to have another "textbox29" outside the table to count the number of "textbox39"s that are actually displayed and also the number of "textbox1"s that have a certain value (e.g. "1") in the final report. I tried to use "Sum(ReportItems!textbox39.Value)" but the compiler complains

Error 1 [rsAggregateReportItemInBody] The Value expression for the textbox 'textbox29' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. d:\perf\perfreportingproject\PerformanceTestDetails v.3.rdl 0 0

Error 7 [rsReportItemReference] The Value expression for the textbox ‘textbox29’ refers to the report item ‘textbox39’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. d:\perf\perfreportingproject\PerformanceTestDetails v.3.rdl 0 0

Anybody has any idea how to solve it?

Thanks so much,

Zhiyan

What are you actually trying to do in terms of the data? Are you trying to count how many groups there are? Or count the number of items in a group? Have you tried using the RowCount or RunningValue function on the data fields?|||

Suppose there is a long expression which displays color "Red" and "Green" in textbox39, I want to count the number of Reds and Greens in my report. Now I can get the total number of textbox39s by multipling the number rows and columns. But when I tried to count the colors, the compiler complains:

Error 1 [rsAggregateofAggregate] The Value expression for the textbox 'textbox27' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. d:\perf\perfreportingproject\PerformanceTestDetails v.3.rdl 0 0

Error 2 [rsAggregateofAggregate] The Value expression for the textbox 'textbox27' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. d:\perf\perfreportingproject\PerformanceTestDetails v.3.rdl 0 0

Any ideas?

Thanks!

How to count the number of occurences of a value in a column

I have a report, for which I want to keep track of the number of times a value is recurring.

eg.

Column A Column B Column C

A 5 0

B 3 0

A 0 1

B 1 0

C 0 2

D 0 3

So, in this example, I want Column C to keep a running count of the number of times zero occurs in Column B.

I was thinking about using a global variable and increment it whenever column B's value was 0 but I haven't a clue as to where to begin. Any help will be appreciated

Thanks

Use a conditional RunningValue in a table detail column to calculate the values as shown in column C:

=RunningValue(iif(Fields!B.Value = 0, 1, 0), Sum, Nothing)

-- Robert

|||

Thanks A million Robert!!!!

Such a simple solution, never thought of that. Thanks again.

how to count rows in field

Hello.
I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to
the group field or somw where in the report.
How can I count how many rows do I have in each group?
Thanks.On May 1, 3:44 am, nicknack <roezo...@.gmail.com> wrote:
> Hello.
> I built a report with one field as a group.
> I want to count the number of rows in each field so I can add it to
> the group field or somw where in the report.
> How can I count how many rows do I have in each group?
> Thanks.
I'm not sure if it is accessible by group, but you could try using the
'rownumber' keyword. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
If you want to show the total group by:
In the group header write a expression like
=count(Field!Yourcolumn.Value)
If you want distinct count use this expression
=countdistinct(Field!yourcolumn.value)
Regards,
Sri
"nicknack" wrote:
> Hello.
> I built a report with one field as a group.
> I want to count the number of rows in each field so I can add it to
> the group field or somw where in the report.
> How can I count how many rows do I have in each group?
> Thanks.
>|||Hi Martinez
Thanks for the replay.
I used :
=3DCountRows("GroupName")
in the group row and it does the work.
Thanks :)
EMartinez =D7=9B=D7=AA=D7=91:
> On May 1, 3:44 am, nicknack <roezo...@.gmail.com> wrote:
> > Hello.
> >
> > I built a report with one field as a group.
> > I want to count the number of rows in each field so I can add it to
> > the group field or somw where in the report.
> >
> > How can I count how many rows do I have in each group?
> >
> > Thanks.
>
> I'm not sure if it is accessible by group, but you could try using the
> 'rownumber' keyword. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant

How to count number of rows

Hello, Dean
Try this:

select distinct c1, c2 into #tmp_1 from t1
select count(*) as cnt from #tmp_1
drop table #tmp_1

With best regards.how to count number of rows?

well, try this...

select count(*)
from <yourtable>

How to count number of Groups in MS-SQL ?

The following SQL works on Access and Oracle to return the number ofGroups(Rows) of the SQL. In MS-SQL this SQL is not valid. What is theequivalent in MS-SQL?
Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight]from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion )
Thanks,

Frankk
Hello,
this seems to work:
> Select Count(1) FROM (Select ShipRegion, Sum(FREIGHT) as [TotalFreight] from ORDERS WHERE OrderID < 123456 GROUP BY ShipRegion) AS X

Hope this helps. -LV
|||LV,
Thanks, it works !!!!!

Frankk

How to count no of items in nested table

I have the following table

Region Table

ID

ParentID

RegionName

RestaurantTable

ID

RestaurantName

RegionID

What i tried to do is count the number of restaurants by specific regionname. My current query is

Select RegionID, RegionName, count(*) as RestaurantNo

From Region Inner Join Restaurant

On Region.ID = Restaurant.RegionID

However I only get the results below

RegionID RegionName RestaurantNO

1 A1 0

2 A1.1 2

3 A1.2 1

4 A1.3 0

Where A1.1 , A1.2, and A1.3 are children of A1 in Region table

The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3

Could anyone help me to solve this problem.

Thank you

You need to account for the hierarchy in your join. If you only have 2-deep regions then a simple OR base on ParentID will take care of the problem; however, if your nesting can go more than 2 deep you ought to use a CTE to account for all ancestor possibilities.

The simple alternative would append the OR condtion to your ON condition; something like:

Code Snippet

ON Region.id = Restaurant.RegionId
OR Region.ParentID = Restaurant.RegionId

If you need to take the CTE route, do a search of this forum for HIERARCHY to get some ideas.|||

I need to do on nested query and I tried to search for CTE but I couldn't get the job done, It's always return the incorrect results

WITH RegionRestaurantCount (RegionID, RegionName,ParentID, NumberOfRestaurants) AS
(
SELECT
RegionID,
RegionName,

ParentID,
(SELECT COUNT(*) FROM Region node
WHERE node.RegionID = parent.RegionID) as NumberOfRestaurants
FROM Region parent
)

SELECT rc.RegionName, rc.NumberOfRestaurants
FROM Restaurant res
INNER JOIN RegionRestaurantCount rc ON
res.RegionID = rc.RegionID
ORDER BY RegionName

Could you please help? Thank you|||

Try creating a function that returns a table including each row with its children, including itself. Then use that table to join to [restaurant] and do the aggregation.

Code Snippet

use tempdb

go

create table dbo.region (

ID int not null primary key,

ParentID int null references dbo.region(ID),

RegionName varchar(25) not null

)

go

create table dbo.restaurant (

ID int not null primary key,

RestaurantName varchar(25) not null,

RegionID int not null references dbo.region(ID)

)

go

insert into dbo.region values(1, null, 'A1')

insert into dbo.region values(2, 1, 'A1.1')

insert into dbo.region values(3, 1, 'A1.2')

insert into dbo.region values(4, 1, 'A1.3')

go

insert into dbo.restaurant values(1, 'r1', 2)

insert into dbo.restaurant values(2, 'r2', 2)

insert into dbo.restaurant values(3, 'r3', 3)

go

create function dbo.ufn_children (

@.ID int

)

returns table

as

return (

with cte

as

(

select ID, ParentID

from dbo.region

where ID = @.ID

union all

select

c.ID, c.ParentID

from

cte as p inner join dbo.region as c

on c.ParentID = p.ID

)

select *

from cte

)

go

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

)

select

c.pID,

count(*) as cnt

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by

c.pID

order by

c.pID

option (maxrecursion 0)

go

drop function dbo.ufn_children

go

drop table dbo.restaurant, dbo.region

go

AMB|||

Thank you for your solution however could you explain more details about the logic behind the function do, i am a little bit lost,also how can i translate it into stored procedure that I will use to retrieve the no_of_restaurants based on the regionID.

|||

1 - The function uses a recursive CTE to pull an [ID] and all its children, including itself. If you use that function to pull all childrens for every row in table [region], you will get something like this:

Example:

1 - 1

1 - 2

1 - 3

1 - 4

2 - 2

3 - 3

4 - 4

2 - If you join previous result to the table [restaurant], you will get:

1 - 2 - 1

1 - 2 - 2

1 - 3 - 3

2 - 2 - 1

2 - 2 - 2

3 - 3 - 3

so, if you group by first [ID] then you will get

1 - 3 rest

2 - 2 rest

3 - 1 rest

3 - Here is the sp

Code Snippet

create procedure dbo.usp_p1

@.ID int,

@.cnt int output

as

set nocount on

;with bridge

as

(

select

a.ID as pID,

b.ID as cID

from

dbo.region as a

cross apply

dbo.ufn_children(a.ID) as b

where

a.[ID] = @.ID

)

select

@.cnt = count(*)

from

bridge as c

inner join

dbo.restaurant as d

on d.RegionID = c.cID

group by c.pID

option (maxrecursion 0)

go

declare @.cnt int

exec dbo.usp_p1 1, @.cnt output

print @.cnt

go

AMB

How to count

Hello all!
What is a neat way to count the number of children for every parent in
code
below?

CREATE TABLE parent
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

parent_name VARCHAR(100)
)
GO

CREATE TABLE children
(
parent_row_id INTEGER,
child_name VARCHAR(100)
)

INSERT INTO parent (parent_name) VALUES ('King')
INSERT INTO parent (parent_name) VALUES ('Farmer')
INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)Select parent_name, SubQuery.Sum_of_children from parent
INNER JOIN
(
Select parent_row_id,COUNT(*) from children group by
parent_row_id
) SubQuery
ON SubQuery.parent_row_id = parent.row_id

HTH, Jens Suessmeyer.|||SELECT parent_name,
(SELECT COUNT(parent_row_id)
FROM children
WHERE parent_row_id = row_id) AS number_of_children
FROM parent

Good luck,
Tony Sebion

"jonsjostedt@.hotmail.com" <jonsjostedt@.hotmail.com> wrote in message
news:1126624285.607326.195420@.g44g2000cwa.googlegr oups.com:

> Hello all!
> What is a neat way to count the number of children for every parent in
> code
> below?
> CREATE TABLE parent
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> parent_name VARCHAR(100)
> )
> GO
> CREATE TABLE children
> (
> parent_row_id INTEGER,
> child_name VARCHAR(100)
> )
> INSERT INTO parent (parent_name) VALUES ('King')
> INSERT INTO parent (parent_name) VALUES ('Farmer')
> INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)