Returning multiple values in recordset form

Hope you can help.
Classic ASP / SQL Server 2000
I'd like my proc to return a recordset with 3 columns.
The values of these 3 columns are each generated by a very
different SQL statement.
I cannot figure out how to write all this so it returns a
nice recordset...
Here's the code -- any suggestions will be greatly appreciated.
CREATE PROCEDURE proc_GetStats
(
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @.SQLA nvarchar(2500)
DECLARE @.SQLB nvarchar(2500)
DECLARE @.SQLC nvarchar(2500)
DECLARE @.INTA int
DECLARE @.INTB int
DECLARE @.INTC int
--Those are actually 3 really different select statements
SET @.SQLA = 'SELECT from TABLE_A returns integer'
SET @.SQLB = 'SELECT from TABLE_B returns integer'
SET @.SQLC = 'SELECT from TABLE_C returns integer'
exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
--How to return this in a recordset form?
SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
procEnd:
END
GO
****** What I currently get from running this *************
13556
0
3
INTA INTB INTC
-- -- --
0 0 0Terry,
What do you mean by "a nice recordset"?
If you want to return scalar values to your client app, it is better to
return them as output parameters instead returning them in a recordset.
ADO Performance Best Practices
http://msdn.microsoft.com/library/d.../>
estprac.asp
Best Practices for Using ADO.NET
http://msdn.microsoft.com/library/d.../>
netbest.asp
AMB
"Terry" wrote:

> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Your SELECT statements need to assign the scalar result. Otherwise, the
results are returned to the client. For example:
SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
Hope this helps.
Dan Guzman
SQL Server MVP
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> ( )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks Dan, I had already tried that and it returns errors:
Must declare the variable '@.INTA'
Must declare the variable '@.INTB'
Must declare the variable '@.INTC'
which makes sense since in the context of the string, the
variable is not declared.
Any other ideas will be appreciated. BTW, the reason I'd
like this returned in recordset form is for compatibility
with existing code. Thanks for your understanding.
Original :: Dan Guzman :: 2005-03-15 08:58

> Your SELECT statements need to assign the scalar result. Otherwise, the
> results are returned to the client. For example:
> SET @.SQLA = 'SELECT @.INTA = MyColumn FROM TABLE_A'
> SET @.SQLB = 'SELECT @.INTB = MyColumn FROM TABLE_B'
> SET @.SQLC = 'SELECT @.INTC = MyColumn FROM TABLE_C'
>|||Is there any way you can type the select statements directly and not use
sp_executesql?
If you use a statement like
SELECT @.INTA=column FROM TableA
It doesn't return a result set and therefore won't be sent back to the ASP
page as a recordset. The very simple queries you gave in your example
certainly don't need to be executed as dynamic SQL.
If you can't, you can use the NextRecordset method of the Recordset object.
WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
Recordset objects are expensive to create.
Ex:
...snip...
Set myRS = myCommand.Execute() 'Opens the recordset created from retrieving
IntA
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntB
Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
retrieving IntC
Set myRS = myRS.NextRecordset() 'Here you are!
"Terry" <a165287@.yahoo-youknow.com> wrote in message
news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
> Hope you can help.
> Classic ASP / SQL Server 2000
> I'd like my proc to return a recordset with 3 columns.
> The values of these 3 columns are each generated by a very
> different SQL statement.
> I cannot figure out how to write all this so it returns a
> nice recordset...
> Here's the code -- any suggestions will be greatly appreciated.
>
>
> CREATE PROCEDURE proc_GetStats
> (
> )
> AS
> SET NOCOUNT ON
> BEGIN
> DECLARE @.SQLA nvarchar(2500)
> DECLARE @.SQLB nvarchar(2500)
> DECLARE @.SQLC nvarchar(2500)
> DECLARE @.INTA int
> DECLARE @.INTB int
> DECLARE @.INTC int
>
> --Those are actually 3 really different select statements
> SET @.SQLA = 'SELECT from TABLE_A returns integer'
> SET @.SQLB = 'SELECT from TABLE_B returns integer'
> SET @.SQLC = 'SELECT from TABLE_C returns integer'
> exec sp_executesql @.SQLA, N'@.TOTO int output', @.INTA output
> exec sp_executesql @.SQLB, N'@.TOTO int output', @.INTB output
> exec sp_executesql @.SQLC, N'@.TOTO int output', @.INTC output
> --How to return this in a recordset form?
> SELECT @.INTA 'INTA', @.INTB 'INTB', @.INTC 'INTC'
>
> procEnd:
> END
> GO
>
> ****** What I currently get from running this *************
>
> --
> 13556
>
> --
> 0
>
> --
> 3
> INTA INTB INTC
> -- -- --
> 0 0 0
>|||Thanks! That did the trick :)
Original :: CMBergin :: 2005-03-16 08:54

> Is there any way you can type the select statements directly and not use
> sp_executesql?
> If you use a statement like
> SELECT @.INTA=column FROM TableA
> It doesn't return a result set and therefore won't be sent back to the ASP
> page as a recordset. The very simple queries you gave in your example
> certainly don't need to be executed as dynamic SQL.
> If you can't, you can use the NextRecordset method of the Recordset object
.
> WARNING - this is undesirable if you can do ANYTHING AT ALL to avoid it.
> Recordset objects are expensive to create.
> Ex:
> ...snip...
> Set myRS = myCommand.Execute() 'Opens the recordset created from retrievin
g
> IntA
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntB
> Set myRS = myRS.NextRecordset() 'Moves to the recordset created from
> retrieving IntC
> Set myRS = myRS.NextRecordset() 'Here you are!
> "Terry" <a165287@.yahoo-youknow.com> wrote in message
> news:SjBZd.645$yD.3435@.newscontent-01.sprint.ca...
>