run 3 dynamic selects in mssql7

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

run 3 dynamic selects in mssql7

Author

Topic

drdave
Starting Member

2 Posts

Posted - 2004-09-30 : 15:45:22

I am trying to run 2 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

It doesn't throw an error only returns first result..

Is there a better way of doing this??

tia

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-09-30 : 15:52:36

If you run this proc in QA, does it give you both results (I bet it does).
What app / language are you using to grab this result set? If it's ADO 2.X I think you need to call NextRecordset() to get the second result set.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-30 : 16:45:28

why does your data model require that a tablename is passed in to a stored procedure? or is this just a maintaince/DBA thing?

Why must this stored proc search multiple tables? Why isn't all the data in 1 table?

- Jeff

drdave
Starting Member

2 Posts

Posted - 2004-10-01 : 09:29:54

thx for the replies..

I'm using ColdFusion to run the proc.. so it is a webapp.

Here is a little more background. I have several views based on yearly requests that I need to run yearly reports on, to run this report I am passing the year in from a form to dynamically get the proper Viewname/TableName.

ie @Tablename = requests_2003.

The data is in 1 table, actually a view.. I dont write a lot of stored procs so I'm looking for some syntax pointers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources