?
Solved

RETURNING RECORDSET FROM SQL SERVER STORED PROCEDURE?

Posted on 2005-03-31
5
Medium Priority
?
279 Views
Last Modified: 2006-11-18
HI EVERYONE,

I want to return a recordset from the Sql Server stored procedure.
The stored procedure is as follows:

// Little description about the stored Procedure is here
I have table named ABC from which i store the data in a CURSOR. And on the data of cursor i want to run another query on table named XYZ whose result i want to return in my ASP.
This procedure is working fine in SQL Server Query analyzer but didn't return any value in ASP.

CREATE PROCEDURE [dbo].[Week52Hi] AS
begin
      declare @Company varchar(30)
      declare @V_MAX(HI) INT
      
-- MY MAIN QUERY
DECLARE RS3 CURSOR FOR
(      SELECT Company, MAX(HIGHEST) MaxHigh
      FROM ABC
      WHERE
      DATE_TIME >= convert(char(12),dateadd(year,-1,getdate()),101) AND
      DATE_TIME < CONVERT(varchar,GetDate()-1,101)
      group by Company
      ORDER BY Company
)

Open RS3
      Fetch from RS3
      INTO @Companye,@V_MAX

while @@fetch_status = 0
 begin
      
      -- MY SUB-QUERY
      SELECT CompanyCode, Hi AS MaxHigh
      FROM ABC where date_time = CONVERT(char,getdate(),101)
      AND CompanyCode=@Company
      AND DATE_TIME = (SELECT Max(Date_Time) FROM ABC WHERE CompanyCode=@COMPANY)
      AND HI > @v_max ORDER BY Company


       Fetch from RS3
       INTO @Company,@v_max
          
 end

close RS3
deallocate RS3

end
GO


Thanx in Advance.
Waiting for your kind replys.

Regards,
Rafay

0
Comment
Question by:rafayansar
4 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 13670330
You would normally use the command object to do so for example:

            objCmd.Activeconnection = objCon
            objCmd.CommandText = "StoredProcedureName( PARAMETERS )"
            objCmd.CommandType = 04
            objCmd.CommandTimeout = 300
            Set objrs = objCmd.execute

Now the results of the sp are in a recordset called objRs
0
 
LVL 11

Expert Comment

by:Otana
ID: 13670492
If I understand your code correctly, you're executing multiple selects (because of your loop). I think ASP will only get the results of the first select.
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13670512
Only a gues but there is normally a switch for data retrieval with some control systems, something like ExecuteNoRecords. You might want to check that this isn't switched on.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 375 total points
ID: 13670557
Hi rafayansar

Always include a

SET NOCOUNT ON

statement at the beginning of you stored procedures when they are intended to be called from ASP code.

When a procedure returns several recordsets, which is obviously the case here, you can loop through returned recordsets using the NextRecordset method of the Recordset Object

'process first recordset
while not rs.EOF
   ' do whatever you want
wend
'get second recordset
Set rs = rs.NextRecordset
'process second recordset
while not rs.EOF
   ' do whatever you want
wend
' and so on ....
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question