ASP Function that returns result from stored procedures

Question

I am working on a project that requires me to hop into to separate DB's.

So I have figured that I need to have multiple functions inside of my VB page.

The only problem I am having,is I am not to sure how to get this all accomplished.

So far I have figured out the overall structure, just need help implementing that structure.

Here is my idea:

The main Function would call two other functions. We can Call them Sub Function 1 and Sub Function 2.

So, the main Function takes the saved sessions information for the E-mail address and dumps in into Sub Function 1. It needs to open up a new connection to the db/stored procedure and RUN the following procedure and then return the result. Here is the stored procedure and what i think is correct.

CREATE PROCEDURE WEB_User
(
    @EMAIL_ADDRESS varchar(80) = [EMAIL_ADDRESS]
)
AS
SELECT
    MEMBER_NUMBER
FROM
    WEB_LOGIN
WHERE
    EMAIL_ADDRESS = @EMAIL_ADDRESS

So my question is, what is the function suppose to look like? how do I send the session information to the procedure? and finally, how do I return the stored procedure results and push back into the main function so it can be carried into sub function 2?

Thank you in advance for your help... I really appreciate it!

Answer 1

You're asking for a whole swath of code here. I would start with looking at the ADO.NET reference for the basics of how to call stored procedures from code.

Reference: http://msdn.microsoft.com/en-us/data/aa937722.aspx

Another direction you could go with this is to realize that you can cross database boundaries inside of stored procedures by fully qualifying the table name you want to select from.

Use DatabaseA
GO

Select * From
TableA 
INNER JOIN DatabaseB.dbo.TableB B ON TableA.ID = B.ID

dbo here is the owner or schema of the object, so if you've created your table in a different schema or with a different owner, then substitute that in its place.