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 |
|
dcarlton
Starting Member
5 Posts |
 Posted - 2006-01-31 : 15:44:13
|
| Hi...Need some help passing in a table name to stored procedure. I am in the middle of porting a system that used to pass in SQL statements directly to ones that uses stored procedure.I want to do something like:CREATE PROCEDURE dbo.[Term Detail - All Not Installed]@db_prefix varchar(50) AS Begin SELECT * FROM 'dbo.' + db_prefix + 'RET_DATA' WHERE (agentStatus <> N'inactive') AND (dateTermInstalled IS NULL)EndGOAs it sits right now, it does not work. I am trying to have the underyling code pass is a prefix, for instance, if db_prefix = WA, then the table name would be 'WA_RET_DATA'. Make sense?I am new the world of stored procs, so any help would be apprecitated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 15:47:58
|
| You will need to use dynamic SQL for this. You can search the articles on the main page here for how to do it.But you might as well use inline sql in your application rather than stored procedures if your queries are going to be like this.Tara Kizeraka tduggan |
 |
|
|
dcarlton
Starting Member
5 Posts |
Posted - 2006-01-31 : 15:56:08
|
| I need to move over to stored procs because I need to pass in other paramters, not just the prefix. In the current model, I can not pass in parameters because of other limiting factors. This was just a basic example, but I wanted to start with the part that I felt was going to give me the most trouble. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 16:00:28
|
quote:
I need to move over to stored procs because I need to pass in other paramters, not just the prefix.
You should not move to stored procedures just because you need to pass in other parameters. You can do this with inline sql. You will receive a performance hit and have weaker security if you use dynamic SQL in stored procedures.Tara Kizeraka tduggan |
|
|
|
dcarlton
Starting Member
5 Posts |
Posted - 2006-01-31 : 16:15:12
|
| Okay, maybe you can provide me some direction then. These SQL statements (whether straight SQL or stored procs) are executed via C# and VB.Net, in the ASP.Net framwork. Currently, the SQL text is stored in a database table, and are used for creating reports. So, the user goes to a webpage that lists all the reports they have access to. The same webpage is used for all users, and other security mecahnisms control access.So, the user is presented with a list of reports with verbose names. But, since these are stored as text in a table, I can not pass in values . When the user selects a report, I grab the SQL text via code from the database, then make a connection to the appropriate table (controlled by the prefix) to grab the data and push it out as a csv file.My thought is, if I use stored procs, I can pass in paramters, something I can not do without a lot of parsing of the SQL text, which I do not want to do. I want to start providing more detailed and customized reports, which I thought was leading me down this road.Security is not too much of an issue, as the webserver and db server are seperate, and there is a proxy protecting the webserver.So, any advice or guidance would be appreciated. Thanks for the dynamic SQL advice, BTW, as that did work. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-31 : 16:19:45
|
| You don't have one RET_DATA table per customer or something like that, do you? Why do you have these prefixes on your table? table names should be *constants* -- only the data itself should change.If you are storing data that is similiar but only different due to a prefix or a tag, it should all be in one table *including* a column for the prefix or tag in the data. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 16:21:52
|
| Why is the query stored in a table? Can you change that part of the design?Security is still a concern as one of the best reasons to use stored procedure is for security. Stored procedures allow you to grant EXEC on only those objects. Users do not need direct table level access, except in the case of dynamic SQL as that code isn't run in the same context as the stored procedure.Tara Kizeraka tduggan |
|
|
|
dcarlton
Starting Member
5 Posts |
Posted - 2006-01-31 : 16:35:54
|
| I will answer a couple of questions...Yes, there is one table per customer, but this was done because of the nature of the data. This data contains some sensitive information, and one of the easiest ways to seperate it was via seperate tables. The data in each seperate table is no way related to one another and is never combined. So, to put it a different way, there is no relationship between the data in any of the tables. All the data is similiar, yes, but our customers would not like it very much if it was "mingled" with other data.The reason there are prefixes, because the web-reporting piece can access any number of these tables, and it choses which one based upon permission set-up via an authentication piece in ASP.Net. The prefixes are a result of the web-design. Also, it cuts down on the amount of code that has to be written if all the tables are the same basic name, with the exception of a prefix. That, and the acutal app that enters the app was written so that a prefix can be used, making the app a "write once, deploy anywhere" deal. Cuts down on maint costs.No one has access to the real tables, and they can not do SQL injection, as that is blocked at the proxy.I could possible change the part about storing the queries in a table, but the issue is that of flexibility. I need to be able to add new queries (or reports) on the fly, and assign them to specific users or groups. So user A, only has access to the reports he needs, and user B only has access to his. Since they are in a table, I can input new ones as needed, or remove ones as needed, without recompiling code.If someone has a better idea on where to store the data, I am all ears. But I can not think of a way that either does not involve some sort of file, database or hardcoding them into the actual underlying code.Also, I do not think this can not be done via security on the actual db server, because all queries are executed by the ASP.Net account, which is used natively by .Net. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 16:47:56
|
| Regardless of the nature of the data, the design should not have been one table per customer. There are other ways to ensure that one customer never sees another customer's data. We have legal reasons why customers can see each other's data, so we code for it and test it. You will find that with your current design that you are having to jump through hoops to get it to work. With all of that being said, I can't offer up much help, except to say that you'll be using lots of dynamic SQL, given your current design.Tara Kizeraka tduggan |
|
|
|
dcarlton
Starting Member
5 Posts |
Posted - 2006-01-31 : 16:54:51
|
| Okay, thanks for the insight. |
|
|
|
|
|
|
|
|
Stored Proc - Passing In Table Name