Function to Return Stored Procedure Record Set

[SethM]

I have a stored procedure that returns a record set. I want to
functionalize this so I can have multiple presentations of the same
record set. However, I can not get rs_event.open StoreProc to pass
through the function, so I can use rs_event("Title"), etc, etc. Is
this possible to do? If so how? Thanks.

Regards,

Seth

 

[Bob Barrows [MVP]]

I have a stored procedure that returns a record set. I want to
functionalize this so I can have multiple presentations of the same
record set. However, I can not get rs_event.open StoreProc to pass
through the function, so I can use rs_event("Title"), etc, etc. Is
this possible to do? If so how? Thanks.

Not sure what you mean by "functionalize" or "multiple presentations of the
same record set" (why would you want to do that??)

Maybe if you show some code, we can get a better idea of what you are trying
to do.

 

[SethM]

Not sure what you mean by "functionalize" or "multiple presentations of the
same record set" (why would you want to do that??)

Maybe if you show some code, we can get a better idea of what you are trying
to do.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks for the reply. The following is what I have now.

Function DisplayEvents(strEventType)

Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
set rs_event = server.createobject("ADODB.recordset")
With cmdStoredProcEvent
.ActiveConnection = conn
.CommandText = "get_event_list_sp"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@retval", adInteger,
adParamOutput)
'add input parameters
.Parameters.Append .CreateParameter("@prod_fam", adVarChar,
adParamInput,150,strProductFamily)
.Parameters.Append .CreateParameter("@event_type", adVarChar,
adParamInput,15,strEventType)
.Execute, , adExecuteNoRecords
retval = .Parameters("@retval")
End With
IF strEventType = "webcast" THEN

response.Write("<h5> Webcasts</h5>")
rs_event.Open cmdStoredProcEvent

....displays values from the recordset

End Function

I would like to remove the display of the recordset from the function
to enable me to have different presentations of the information. I
would like to see if I could just call DisplayEvents(strEventType) and
get the object rs_event and control the display of the information at
the page level rather than at the function level. Thanks for any help.

Regards,

Seth

 

[Bob Barrows [MVP]]

Thanks for the reply. The following is what I have now.

Function DisplayEvents(strEventType)

Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
set rs_event = server.createobject("ADODB.recordset")
With cmdStoredProcEvent
.ActiveConnection = conn
.CommandText = "get_event_list_sp"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@retval", adInteger,
adParamOutput)
'add input parameters
.Parameters.Append .CreateParameter("@prod_fam", adVarChar,
adParamInput,150,strProductFamily)
.Parameters.Append .CreateParameter("@event_type", adVarChar,
adParamInput,15,strEventType)
.Execute, , adExecuteNoRecords
retval = .Parameters("@retval")
End With
IF strEventType = "webcast" THEN

response.Write("<h5> Webcasts</h5>")
rs_event.Open cmdStoredProcEvent

...displays values from the recordset

End Function

I would like to remove the display of the recordset from the function
to enable me to have different presentations of the information. I
would like to see if I could just call DisplayEvents(strEventType) and
get the object rs_event and control the display of the information at
the page level rather than at the function level. Thanks for any help.

Regards,

Seth

So add

Set DisplayEvents = rs_event

before the End Function line so the function can return the recordset to the
caller, which calls it by:

Set rs = DisplayEvents(eventtype)

I'm still not clear what you are trying to do. Why would a single page need
to display the same recordset data in multiple ways? I hope you're not
planning to call this function multiple times in the same page ... it would
be extremely wasteful to make several trips to the database to retrieve the
same set of data ...

Perhaps you should leave this function as is and declare a page-level
recordset variable, allowing you to call the function once.
I would also suggest you use a disconnected recordset so you can close your
connection while processing the recordset.

 

[Bob Lehmann]

Not sure what you mean by "functionalize" or "multiple presentations of the
same record set" (why would you want to do that??)

Maybe if you show some code, we can get a better idea of what you are trying
to do.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Thanks for the reply. The following is what I have now.

Function DisplayEvents(strEventType)

Set cmdStoredProcEvent = Server.CreateObject("ADODB.Command")
set rs_event = server.createobject("ADODB.recordset")
With cmdStoredProcEvent
..ActiveConnection = conn
..CommandText = "get_event_list_sp"
..CommandType = adCmdStoredProc
..Parameters.Append .CreateParameter("@retval", adInteger,
adParamOutput)
'add input parameters
..Parameters.Append .CreateParameter("@prod_fam", adVarChar,
adParamInput,150,strProductFamily)
..Parameters.Append .CreateParameter("@event_type", adVarChar,
adParamInput,15,strEventType)
..Execute, , adExecuteNoRecords
retval = .Parameters("@retval")
End With
IF strEventType = "webcast" THEN

response.Write("<h5> Webcasts</h5>")
rs_event.Open cmdStoredProcEvent

....displays values from the recordset

End Function

I would like to remove the display of the recordset from the function
to enable me to have different presentations of the information. I
would like to see if I could just call DisplayEvents(strEventType) and
get the object rs_event and control the display of the information at
the page level rather than at the function level. Thanks for any help.

Regards,

Seth

Why not pass the recordset to functions that only do one thing (cohesion),
and have each function have different output?

Bob Lehmann

 

[SethM]

So add

Set DisplayEvents =  rs_event

before the End Function line so the function can return the recordset to the
caller, which calls it by:

Set rs = DisplayEvents(eventtype)

I'm still not clear what you are trying to do. Why would a single page need
to display the same recordset data in multiple ways? I hope you're not
planning to call this function multiple times in the same page ... it would
be extremely wasteful to make several trips to the database to retrieve the
same set of data ...

Perhaps you should leave this function as is and declare a page-level
recordset variable, allowing you to call the function once.
I would also suggest you use a disconnected recordset so you can close your
connection while processing the recordset.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hi Bob and Bob

**Side note - thoughts of Office Space running through my head
now...haha, anyway I digress.

Thank you for your responses. After reading my posts, I am not sure I
explained what I am doing well enough. So let me try again.

I have a stored procedure which gets a event type input to it and
returns the necessary information based on that input. I have this
stored procedure within a function so that whenever I want to output
the information to any part of page on my site I can. In an effort to
separate data from presentation, I would like to have the specific
page that I am calling the function on handle the display of the
return record set. There are potentially three different types of
events. Webcasts, Tradeshows and training. So on a single page I could
be calling the function between 1 and 3 times. I tried returning the
recordset like Set rs = DisplayEvents(eventtype), however I got an
error of the response buffer reaching its limit. I want to do this
because on different pages I want the recordset displayed within a
table, while on another page I may want it displayed in a list. I hope
this helps to clarify what I am trying to do with this function.
Thanks for the help.

Regards,

Seth

 

[Bob Barrows [MVP]]

Hi Bob and Bob

**Side note - thoughts of Office Space running through my head
now...haha, anyway I digress.

Thank you for your responses. After reading my posts, I am not sure I
explained what I am doing well enough. So let me try again.

I have a stored procedure which gets a event type input to it and
returns the necessary information based on that input. I have this
stored procedure within a function so that whenever I want to output
the information to any part of page on my site I can. In an effort to
separate data from presentation, I would like to have the specific
page that I am calling the function on handle the display of the
return record set. There are potentially three different types of
events. Webcasts, Tradeshows and training. So on a single page I could
be calling the function between 1 and 3 times. I tried returning the
recordset like Set rs = DisplayEvents(eventtype), however I got an
error of the response buffer reaching its limit. I want to do this
because on different pages I want the recordset displayed within a
table, while on another page I may want it displayed in a list. I hope
this helps to clarify what I am trying to do with this function.
Thanks for the help.

How many records could be potentially returned for each event type?
Frankly, I've never seen a response buffer limit error from opening a
recordset!

Now it sounds as if you need to use this function on multiple pages, so
it needs to live in a server-side include file (SSI), right?