Automatic table filter - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Automatic table filter

Author

Topic

beroetz
Starting Member

2 Posts

Posted - 2008-04-15 : 11:32:52

Hello,

Here is my problem:

I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)
create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)

Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABC
as
begin
/*
some checks and expressions here
...
*/
select ...
from A inner join
B on (A.ID = B.REF_ID)
where ...
/* ... */
end;

All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.

However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.

Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.

However, I don't know what is the easiest and fastest way to filter the tables. Example:

I modified the above mentioned procedure in the following way:
create procedure ABC
as
begin
/*
some checks and expressions here
...
*/
-- gets the CompanyID from the context:
DECLARE @CompanyID int;
SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ...
from A inner join
B on (A.ID = B.REF_ID)
where ...
and A.COMPANYID = @CompanyID
and B.COMPANYID = @CompanyID
/* ... */
end;

Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".

I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.

I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?

Thanks in advance.

Best regards,
Beroetz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 11:44:22

Where exactly are you using these queries. If its for a reporting purpose you have ability to filter your data for each company at your front end in reporting services.

beroetz
Starting Member

2 Posts

Posted - 2008-04-16 : 04:23:47

Thanks for the quick reply.

No, we do not use reporting services. The queries are in stored procedures that are called from our front-end application.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-16 : 14:09:38

In my opinion, the correct solution is to pass in the CompanyID and modify all the stored procedures accordingly.

But, an option might be to create a view on Company. maybe something like:

CREATE VIEW CompanyView
AS

SELECT 
	CompanyID,
	-- other columns
FROM Company
WHERE CompanyID = dbo.Context_Info()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources