Strange SP Results 
Author Message
 Strange SP Results

I am having strange results with a stored procedure that returns records
from a view. The view (vwMergeListing) returns about 20 fields. My stored
procedure takes a parameter which determines whether all or some of the
fields are returned (M=some of the fields, F=all of the fields).

The strange thing is that, though the sp is working with the parameter
correctly, it seems stuck on displaying one recordset or the other. That is,
sometimes it displays the "M" recordset, and then, when I pass it "F", it
returns the "F" fields, but only as they match the "M" fields. Similarly,
sometimes it returns all fields in the "F" recordset, and then, when I pass
it "M", it returns the "M" fields but with all "F" fields showing (the "F"
fields that aren't in the "M" recordset display "#Name").

I'm running this from an Access 2000 ADP file. I tried two forms of SQL in
the sp: one with direct SQL, and one with building a SQL string. Below are
the two SPs. Any assistance would be appreciated.

Thanks!

Neil

Alter Procedure "Rep_MergeListing1"

AS
SET NOCOUNT ON


        Select Name, MergeID, Description, LINE1, LINE2, LINE3, ZIPCODE,
DEALRET, [Index]
        From vwMergeListing

        Select * From vwMergeListing

RETURN(0)

On_Error:
RETURN(1)


AS
SET NOCOUNT ON




           When 'M' Then
                'Select Name, MergeID, Description, LINE1, LINE2, LINE3,
ZIPCODE, DEALRET, [Index] ' +
                'From vwMergeListing'
           When 'F' Then
                'Select * From vwMergeListing'
      End



RETURN(0)

On_Error:
RETURN(1)



Wed, 20 Oct 2004 05:45:01 GMT
 Strange SP Results

Quote:

> I am having strange results with a stored procedure that returns records
> from a view. The view (vwMergeListing) returns about 20 fields. My stored
> procedure takes a parameter which determines whether all or some of the
> fields are returned (M=some of the fields, F=all of the fields).

> The strange thing is that, though the sp is working with the parameter
> correctly, it seems stuck on displaying one recordset or the other. That
> is, sometimes it displays the "M" recordset, and then, when I pass it
> "F", it returns the "F" fields, but only as they match the "M" fields.
> Similarly, sometimes it returns all fields in the "F" recordset, and
> then, when I pass it "M", it returns the "M" fields but with all "F"
> fields showing (the "F" fields that aren't in the "M" recordset display
> "#Name").

> I'm running this from an Access 2000 ADP file. I tried two forms of SQL in
> the sp: one with direct SQL, and one with building a SQL string. Below are
> the two SPs. Any assistance would be appreciated.

Did you try running the procedure from Query Analyzer? My guess is that
it is Access that is really prepared to handle that the procedure returns
different result sets.

You may get better answer in Access newsgroup on how to play with Access.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Fri, 22 Oct 2004 04:12:53 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Process sp Result Set Within an sp?

2. Problem with results and SP calling SP in VB

3. Referencing result from SP in SP

4. How to use a result set from one SP in another SP

5. Result of a sp into a temp table within other sp

6. using result of one SP in another SP

7. Returning a result set from a SP from a SP

8. Using result set of sp in sp

9. OpenLink Virtuoso SP + Informix SP = result ???

10. Receiving results of a SP result set

11. Strange Syntax Error when running SP in 6.5

12. URGENT - Strange String Concatenation SP !


 
Powered by phpBB® Forum Software