?
Solved

RETURNING RECORDSET FROM SQL SERVER STORED PROCEDURE?

Posted on 2005-03-31
5
Medium Priority
?
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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