Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
clustaSean
Starting Member
2 Posts |
 Posted - 2009-02-16 : 11:28:34
|
| Hi all, this is my first post here. I am trying to build a CMS which is flexible and can be duplicated as quickly and easily as possible. I also have a multihost server which has a single SQL Server database. I want to access the data using stored procedures, and have written a couple of classes in C# for calling the stored procedures and passing data to and from the SP. I am trying to write them so the table name can be passed as a parameter so that i don't have to duplicate them each time I copy the CMS (I can just pass in the table name from the application and the stored procedures can select the relevant table).Does anyone know if this is possible, or how to do it?Also is it a good way of doing it or is there a better way to write a flexible/dynamic data access system?Thanks for any advice.Sean |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-02-16 : 11:40:25
|
| It's possible but it's (almost certainly) a bad idea. It's much better to have a set of stored procs that each do a specific job with specific tables.Have you any idea of how many possible tables / kind of searches your end users are going to want to perform? Can you identify any particular things that the users are always going to want to do? If so write stored procs that do that and pass in parameters for the WHERE conditions (remember to sanitise those inputs!)If you really must do what you propose then I'd give this resource a good read:http://www.sommarskog.se/dynamic_sql.htmlgood luckCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
clustaSean
Starting Member
2 Posts |
Posted - 2009-02-16 : 12:02:06
|
| Thanks Charlie, I was going to use two tables per CMS (website content management system) ]one table would be for 'articles' and would contain mainly text fields and id numbers the other table for 'resources' an would link to an article (1 article to many resources) in order to pull image paths, music file paths etc. I was going to create 2 new tables for each CMS, and put the table names in the web.config file of the web-app. I have just realised though: I can use the same table and filter results by adding an extra field to the main table. That way I can hard code the table name into the stored procedures.Cheers for the help |
|
|
|
|
|
|
Passing table name to stored procedure