Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^
If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^
|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentia
ted by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature
(just look at the lack
of support in their tools). I see it mostly as a backwards compatibility fea
ture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:
> TIA JTC ^..^

Group by across tables

I have two tables in one table callhist there records for every contact made to a particular prospect and the resulting disposition. In the other table (contacts) there are the records for each prospect containing information about the prospect.

I need to results like this.
Here are the columns I need to Display.

Campaign Name(from Contacts Table)
Dials (Select count(*) from calhist (based on parameters))
Contacts(Select count(*) from calhist (based on parameters))
Pres(Select count(*) from calhist (based on parameters))
Sales(Select count(*) from calhist (based on parameters))

and this is the way I would like it to be displayed

Campaigname|Dials|Contacts|Pres|Sales

and Help would be appreciated.

ThanksFirst you should create your select query, taking into account all the WHERE parametrised clauses, but leaving aside any grouping. Then, on top of this query you should contruct your groupping query, in a "select from select" fashion. In the end you should have something like that:

SELECT Sum(P.field1), Max(P.Field2),... P.KeyFieldx
FROM (SELECT field1, field2, ... KeyFieldx FROM table1 INNER JOIN table2 on... WHERE .... = [Your Param:]) P
Group by P.KeyFieldx

M.