run 3 dynamic selects from stored proc

I am trying to run 3 dynamic selects from stored proc, really only
the table name is dynamic.. Anway I'm kinda lost on how I can
accomplish this.. this is what I have but it only returns the first
result.. that being basic

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

Exec ( @.SQL)

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return

Is there a better way of doing this??

tia

DaveOn 30 Sep 2004 10:12:28 -0700, dave wrote:

> I am trying to run 3 dynamic selects from stored proc, really only
> the table name is dynamic.. Anway I'm kinda lost on how I can
> accomplish this.. this is what I have but it only returns the first
> result.. that being basic
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> Exec ( @.SQL)
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return
> Is there a better way of doing this??
> tia
> Dave

If your client isn't prepared to accept multiple resultsets, then you'll
only see the first one. You could join them together with a union:

CREATE PROCEDURE email_complexity

@.TableName VarChar(100)

AS
Declare @.SQL VarChar(1000)
Declare @.SQL1 VarChar(1000)

Set nocount on

SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
SELECT @.SQL = @.SQL + @.TableName
SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'

SELECT @.SQL = @.SQL + ' UNION ALL '

SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
SELECT @.SQL1 = @.SQL1 + @.TableName
SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'

Exec ( @.SQL1)

Return|||Ross Presser <rpresser@.imtek.com> wrote in message news:<nt2t9zlcjwzx.dlg@.rpresser.invalid>...
> On 30 Sep 2004 10:12:28 -0700, dave wrote:
> > I am trying to run 3 dynamic selects from stored proc, really only
> > the table name is dynamic.. Anway I'm kinda lost on how I can
> > accomplish this.. this is what I have but it only returns the first
> > result.. that being basic
> > CREATE PROCEDURE email_complexity
> > @.TableName VarChar(100)
> > AS
> > Declare @.SQL VarChar(1000)
> > Declare @.SQL1 VarChar(1000)
> > Set nocount on
> > SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> > SELECT @.SQL = @.SQL + @.TableName
> > SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> > Exec ( @.SQL)
> > SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> > SELECT @.SQL1 = @.SQL1 + @.TableName
> > SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> > Exec ( @.SQL1)
> > Return
> > Is there a better way of doing this??
> > tia
> > Dave
> If your client isn't prepared to accept multiple resultsets, then you'll
> only see the first one. You could join them together with a union:
> CREATE PROCEDURE email_complexity
> @.TableName VarChar(100)
> AS
> Declare @.SQL VarChar(1000)
> Declare @.SQL1 VarChar(1000)
> Set nocount on
> SELECT @.SQL = 'SELECT Count(complexity) AS basic FROM '
> SELECT @.SQL = @.SQL + @.TableName
> SELECT @.SQL = @.SQL + ' WHERE len(complexity) = 5'
> SELECT @.SQL = @.SQL + ' UNION ALL '
> SELECT @.SQL1 = 'SELECT Count(complexity) AS moderate FROM '
> SELECT @.SQL1 = @.SQL1 + @.TableName
> SELECT @.SQL1 = @.SQL1 + ' WHERE len(complexity) = 8'
> Exec ( @.SQL1)
>
> Return

But if you do that, you should be aware that what you'll receive in
return is two rows, under the single column "basic". There are ways to
improve this (to either add a second column with the texts "basic" and
"moderate", or by converting it into a single row, with columns
"basic" and "moderate").

If the OP is interested in either of these approaches, reply back
here, and I'll post more.