I have a web service that generates different content based on the URL of the client accessing the web service. This works well...except I've been told to 'move' my business logic to the SQL server, to minimize the network traffic.
My current implementation takes the URL http://first.second.com/one/two/three/four.htm
and splits the string into an array based on the '/' delimiter. Then, starting at the back, I test the file name to see if it matches for 'virtual' page creation (ie. if it's contact.htm, I generate a contact page for the requesting site out of the current phone book...ensuring all contact info is current and up to date). If the file name doesn't match, then I go to the next part of the URL (/three/ in this instance), and do a DB query to see if it exists there. If it does, then I return all of the information in the database, and generate the custom page for the user. If the DB does not have an entry, then I iterate up to the next level (/two/) to see whether that exists in the database.
Rinse and repeat for the entire length of the presented URL.
As I said, I have this working now, with a rather nice recursive method built into the web service...but I've been told NOT to make repeated network calls from the web service to the database. I have another problem too...in that users may well have multiple /three/ sub folders, (ie. /one/three/four.htm, /two/three/four.htm, /four/three/four.htm may ALL exist...and require different content...based on the root folder) but I think I have an approach to deal with that.
What I'm asking / requesting, is a stored procedure that will take a URL (ie. http://first.second.com/one/two/three/four.html
) and will match up to the correct folder part (ie. /three/ or /two/), to determine what information to provide back to the web service.
urlPart varchar(10) - ie. one, two, three, etc. (potentially one/two, two/three)
content varchar(50) - information to return to the web service based on the requesting URL part
Ideally, my web service would call "sp_matchURL(@URL)" and retrieve a dataset with the content that matches based on the first found instance of the url part.
Help! **laugh** As I said, it's frustrating to me to HAVE this solution...and have to redo it all on the SQL server. Thanks in advance to all the experts who take the time to assist...