Calling different stored procedures according to a parameter

[tomouse]

I am using an XSD file to store references to SQL 2008 Stored Procedures. For most of my purposes this works fine. However, I am now working on creating a series of alerts. The rules for these alerts are stored as records in a table. One alert must generate a reminder email 1 month before the expiry of drivers' licences. The driver details (including expiry date) are stored in a table. Another alert generates a reminder email when it has been a year since a member of staff last carried out first aid training.

My ALERTS table stores the rules as fields, for example, values "-1" and "M" indicate that the email should be sent out 1 month before the deadline. The idea is that I can run these values through generic code and it will use the values in the Alerts table to generate the email. But to do this I want to store the name of the Stored Procedure (that each alert should run) as a field in the Alert table. Of course the logic for each alert will be different, so each alert will run a different SP. My question is, is it possible to use the same code to call different Stored Procedures via an XSD dataset?

Not sure if I've explained it clearly but effectively I'm trying to use a parameter which will determine which Stored Procedure to call. I will design the stored procedures to return the exact same data schema - basically just a list of User IDs of the people who fit the condition (e.g. driving licence expiring in one month, 1 year since last first aid training). The generic code can then loop through these users and generate the emails. Is this possible?

 

[tomouse]

Just to elaborate, I create a reference to the tableadapter in the XSD file like this:

Private objAlertaDeadlines As New tbaAlertasDeadlines

. The XSD file holds a query for each alert - each query returns the same schema (with 2 fields: idUser and dtDeadline). I can then pass the name of the query into a function like this:

...
dtDeadlines = ConsultDeadlines(spName)

Private Function ConsultDeadlines(byval spName as string)
     return ConsultDeadlines.spName  '<--- problem here
End function

Of course the ".spName" part doesn't work - intellisense is looking for the actual name of the Query as stored in the XSD file. Is there a way to somehow get VB to evaluate and substitute the value of the string that is passed in?