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

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