nesting stored procedure

hi I have another question to ever so helpful forum
I am trying to nest stored procedure but I guess it is not the way to do it as it does not work:

CREATE PROCEDURE dbo.GetSharesTransactionsbyDates

(
@.Startdate as char(10),
@.Enddate as char(10)

)

AS

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

I get complain that dbo is incorrect syntax

ST.Proc. which I try to call call is basicly a select statement with no parameters:

ALTER PROCEDURE dbo.GetSharesTransactionsData
AS
SELECT TRANS_DATE, TYPE_DESCRIPTION, SHARE_SYMBOL, SHARES_QUANTITY,
ROUND(PRICE_PER_SHARE,2) AS PRICE_PER_SHARE, COMMISSION_VALUE,
STAMP_DUTY,
dbo.GetShareTransactionTotalValue(PRICE_PER_SHARE,STAMP_DUTY,COMMISSION_VALUE,
SHARES_QUANTITY,CASH_AMOUNT, TYPE_DESCRIPTION) AS TOTAL_VALUE,/*calling function*/
ACCOUNT_NAME
FROM SHARES_TRANSACTIONS
WHERE (TYPE_DESCRIPTION = 'Sell') OR
(TYPE_DESCRIPTION = 'Buy') OR
(TYPE_DESCRIPTION = 'Cash Divident')
ORDER BY TRANS_DATEYou cannot really do what you are trying to do:

dbo.GetSharesTransactionsData /*tryting to nest sproc*/
WHERE TRANS_DATE BETWEEN @.Startdate and @.Enddate

You cannot add a WHERE clause like that, and you would need to add EXEC to execute the SP. Can you instead create a SP that accepts two dates as parameters?|||Well I can. The idea is that I first bring all transactions up and then only transactions between the chosen dates so I was trying to avoid repeating the same sql select statement but I can rewrite the original SPROC by adding parameters and additional where clause
Thanks for advice.|||alter the sproc, have it accept paramaters with default values:

create procedure spFoo
@.startDate datetime = '1900-01-01',
@.enddate datetime = '2199-12-31'
AS
--whatever

this way, you have a single sp. existng calls continue to work.|||Aaa, thanks.sql

Nested dataregions

Hi!

I'm having some problems with a basic thing I guess.

I have a table of visits as the whole Dataset. Let's call it group A.

I then group the visits per customer, let's call that group B.

After that i filter out some unwanted visits and call that group C.

The hiarchy then looks like this, A contains B that contains C.

The problem is that I want to know the number of rows in C above the actual table. Something like

CustomerName (John Doe) Number of visits (54 from group C but printed out while in group B)

Visit 1 blablabal
Visit 2 blablabla
... and so on ...

As far as I understand the aggregate functions, you can only use them on the current group or a group above, never below. I tried to make an invisible textbox below C and it works if there is just one customer. If there are more than one customer the sum flipped between all the customers. (A gets the sum of B, and so on).

Thanks
Johan

But you can have multiple C group sections (each time your grouping expression changes), correct? If an outer group can reference an inner group, there will be multiple totals that are only known at runtime.|||

In my case, I only have one C groping per B grouping. Its a table that filters out preknown types of visits. Its the number of rows in this table that I would like to print out above the table in question, inside grouping B. What I did was to create a textbox within the C grouping that I referenced from a B grouping. Works for one (1) customer, but if there are more customers the totals get calculated correctly, but displayed in the wrong order.

I realise that it might be inpossible to do this, but I would hope that I at least could "transfer" a number higher up if I know that it only would be one other C grouping.

What I would like to do is something like this:

(pseudocode)

=Count(Fields!VisitId.Value, "not VisitType=BadVisit")

Am I making sence at all? :)

|||Yes, you are but in general you can have more than one group sections and referencing an inner group is not allowed. Why don't you bring the C totals at the B level in your dataset? A simple scalar function (assuming SQL Server) will do the job.|||

The problem is that I don't deside the datasource at the moment and all I have is a view containing Visits of two types. The first type is carried out visits and the second one is missed visits.

They are not in any specific order and missed and carried out visits could be at random rows.

If I could change the dataset, are you suggesting that I put the total amount of visits with each row per customer and just read the first row by each customer grouping to get the value? Kind of like how they denormalized the name of the customer in every row?

Thanks for a quick answer by the way! :)

|||Correct. In this way, you don't have waste your time to find a hack. In addition, you'll never go wrong by pushing work to the database.