run 3 dynamic selects from stored proc

[dave]

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

[Ross Presser]

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

[Damien]

Ross Presser <rp******@imtek.com> wrote in message news:<nt**************@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.