June 22, 2005 at 2:47 am
Hello,
I need to have a store procedure with input parameter CostomerID, and according this parameter the result will be: select only those columns from those tables that are defined for this customer (dynamically) and update only those columns in the tables allowed for this customer (again dynamically). I need this dynamic select and update because the record set for different customers is different.
Note: Sometime join is needed but sometime is not.
P.S.1. I have created a table Matrix containing CustomerID, TableName, ColumnName and SelectOrUpdate (1-means select and 2 - update)
CustomerID, TableName, ColumnName SelectOrUpdate
1 Tbl1 Col1 1
1 Tbl1 Col2 1
1 Tbl2 Col1 1
1 Tbl3 Col1 1
1 Tbl3 Col2 2
2 Tbl3 Col1 1
2 Tbl4 Col5 1
2 Tbl6 Col4 2
…
…
…
P.S.2. Maybe there are some other solutions for this problem but this is my idea.
Thank you in advance,
June 22, 2005 at 6:39 am
Hello,
You mean that you have x tables with CustomerID and you want the resultset to contain all the rows from all the tables (only those tables) that contain a specific @CustomerID? Or do you want One resultset per table that contains the value? And what would you like the update to do?
June 22, 2005 at 7:23 am
Or do you mean that you want different columns showed for different costumers, for the same select?
June 22, 2005 at 7:38 am
My database contains tables: Accounts, Document, Customers, and Activities. I have created a table (called Matrix) with the structure as it was described. Customers table contains all my customers.
So if CustomerID = 1 the result from procedure (the statements in the procedure) should be:
SELECT d.DocumentNo, a.AccountNo
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
And
UPDATE Account
SET AccountNo = @Var
WHERE Document = @Var1
If CustomerID = 2 then:
SELECT d.DocumentNo, a.AccountNo, AccountName
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
And
UPDATE Account
SET AccountName = @Var2
WHERE Document = @Var1
If CustomerID = 3 then:
SELECT a.Account, a.AccountName, ac.Activity, ac.ActivityName
FROM Account a, Activities ac
WHERE ac.DocumentNo = a.DocumentNo
…
In this case Matrix table will contains:
CustomerID, TableName, ColumnName SelectOrUpdate
1 Document DocumentNo 1
1 Account AccountNo 1
1 Account AccountNo 2
2 Document DocumentNo 1
2 Account AccountNo 1
2 Account AccountName 1
2 Account AccountName 2
…
June 22, 2005 at 7:49 am
This syntaxe is illegal (or dangerously incorrect).
So if CustomerID = 1 the result from procedure (the statements in the procedure) should be:
SELECT d.DocumentNo, a.AccountNo
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
And
UPDATE Account
SET AccountNo = @Var
WHERE Document = @Var1
If CustomerID = 2 then:
what are you trying to do with this code?
June 22, 2005 at 8:12 am
Maybe this will be more crear:
first update record
UPDATE Account
SET AccountNo = 1
WHERE DocumentNo = 100
then return the result to the application
SELECT d.DocumentNo, a.AccountNo
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
AND a.DocumentNo = 100
June 22, 2005 at 8:16 am
Well, then I think the followup question would be: Where do you fit in your original question about CustomerID?
What do you want to pass to your SP to get your resulting actions?
June 22, 2005 at 8:36 am
This procedure will be called from another one, which will determine the customerID and passed it to this procedure.
Finally to be as clear as possible, this procedure, if I’m not using somehow dynamically created statements will look something lake:
CREATE PREOCEDURE Load (@CustomerID)
AS
BEGIN
IF @CustomerID = 1
BEGIN
-- First update record
UPDATE Account
SET AccountNo = 1
WHERE DocumentNo = 100
-- Then return the result to the application
SELECT d.DocumentNo, a.AccountNo
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
AND a.DocumentNo = 100
END
IF @CustomerID = 2
BEGIN
-- First update record
UPDATE Account
SET AccountName = ‘Name’
WHERE DocumentNo = 100
-- Then return the result to the application
SELECT d.DocumentNo, a.AccountNo, AccountName
FROM Document d, Account a
WHERE d.DocumentNo = a.DocumentNo
AND a.DocumentNo = 100
END
...
...
...
END
The main point is that each customer has predefined data set that will be returned by executing of this procedure.
June 22, 2005 at 8:51 am
Why would you use something like this???
Why not make a proc to do the update.
One to do the select then just call 'em in the right order from the main proc. Bam you're done in 2 statements!
What logic needs to be applied that can't make this work?
June 22, 2005 at 9:04 am
The problem is not to have one procedure which will select and update or to have two procedures, one for select and another for update. The problem is how the procedure/s will know what to select or what to update for specific customer.
There are 50 customers and not only previously mentioned tables but there are more then 10 tables, and also there is a problem to add a new customer (in that case I will need to edit the procedures). So the problem is how to define (read from somewhere) which columns from which tables some customer will have access (will be able to see as the result from the procedure) and according to this to create the statement and finally give the result back.
June 22, 2005 at 9:33 am
This seems to me that I would be simpler to have the application do the security work.
Maybe you could add a extra layer of security on the server where you'd put each customer in a group, and grant/deny access to the procs/views accordingly to each group. But this is definitly gonna require a lot of dynamic sql.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply