April 24, 2012 at 4:29 am
poornipmca (4/23/2012)
Thank u RobinI cant use if else statement because there will be a dynamic database creation
and i need acess the function using dynamic sql statement or alternative where
i will use a single function for all databases.
Such "Catch All" Functions/Objects may look elegant but are rarely an optimal solution.
More so, why would you want to create a Database dynamically?
I agree as has been previously stated that this points to a flaw in the overall design/approach.
You probably need to take a fresh look at this.
April 24, 2012 at 5:04 am
Actually my database creation depends upon no of customers i have.
each customers have there own individual tables and i cant maintain all those things in
a single database
April 24, 2012 at 5:51 am
poornipmca (4/24/2012)
Actually my database creation depends upon no of customers i have.each customers have there own individual tables and i cant maintain all those things in
a single database
I can't really see why not but then maybe I'm not fully understanding what you are trying to achieve here?
If this is one of those systems where a Customer would have an account opened and have a database/objects created for them on the fly etc... like a hosting account then I wouldn't do that
at the point the Customer completes the process of opening an account online.
I would send a request/parameters to a queue/table and have some backend process actually manage
database creation etc... you can always send notification to the Customer to let them know when the account set up is complete.
April 24, 2012 at 7:51 am
If the only thing that changes is the database name, you could create a synonym. I wouldn't do that in a function, but you could do that in a stored procedure, prior to running the query with the UDF.
After the query, remove the synonym.
April 24, 2012 at 8:42 am
Steve Cullen (4/24/2012)
If the only thing that changes is the database name, you could create a synonym. I wouldn't do that in a function, but you could do that in a stored procedure, prior to running the query with the UDF.After the query, remove the synonym.
The creation of a synonym would not be not allowed inside a function...no DDL for that matter is allowed inside a function. You would have to use a proc, in which case dynamic SQL would become an option once again.
At the end of the day though we're still talking about a workaround for a poor design. In my opinion a design where each customer has their own database (not truly multi-tenant) ought to be abstracted in the application layer, not the data layer.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 8:51 am
opc.three (4/24/2012)
Steve Cullen (4/24/2012)
If the only thing that changes is the database name, you could create a synonym. I wouldn't do that in a function, but you could do that in a stored procedure, prior to running the query with the UDF.After the query, remove the synonym.
The creation of a synonym would not be not allowed inside a function...no DDL for that matter is allowed inside a function. You would have to use a proc, in which case dynamic SQL would become an option once again.
At the end of the day though we're still talking about a workaround for a poor design. In my opinion a design where each customer has their own database (not truly multi-tenant) ought to be abstracted in the application layer, not the data layer.
You are correct, you cannot do it in a function, but if you're picking nits, you wouldn't have to use a stored proc either :hehe:
In the end, you are absolutely correct, it's a poor design looking for a poor workaround.
April 24, 2012 at 9:05 am
Steve Cullen (4/24/2012)
opc.three (4/24/2012)
Steve Cullen (4/24/2012)
If the only thing that changes is the database name, you could create a synonym. I wouldn't do that in a function, but you could do that in a stored procedure, prior to running the query with the UDF.After the query, remove the synonym.
The creation of a synonym would not be not allowed inside a function...no DDL for that matter is allowed inside a function. You would have to use a proc, in which case dynamic SQL would become an option once again.
At the end of the day though we're still talking about a workaround for a poor design. In my opinion a design where each customer has their own database (not truly multi-tenant) ought to be abstracted in the application layer, not the data layer.
You are correct, you cannot do it in a function, but if you're picking nits, you wouldn't have to use a stored proc either :hehe:
In the end, you are absolutely correct, it's a poor design looking for a poor workaround.
Sorry, not trying to pick nits...just closing the loop on the idea of using a function. Proc or no proc, function or no function, I was just bringing it back to "bad design, rethink approach". Agreed all around on that point...not much more to say.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2012 at 10:50 pm
Lot of customers are involving in our project. if i maintain a single database load will be more .So we have chosen multiple database creation based on customers use.Now what will be the solution for this.
My problem is only in the case of functions. what will be the alternative for this.:w00t:
April 24, 2012 at 11:08 pm
If every customer has their own database, duplicate all functions and stored procedures in each database and let the front end handle the connectionto the correct database depending on customer.
April 24, 2012 at 11:23 pm
The database creation depends on no. of customers.
Eg: 50 customers will have same database after 50 dynamically will create a new database .
here all the 50 customers will have different tables with in single database where i have to pass table name has parameter.
April 24, 2012 at 11:46 pm
When you say different tables, are you saying each customer has different tables even withing the same database?
April 25, 2012 at 12:03 am
yes each customer will have different tables.But only 50 customers table will be there in that database .
After that a new database will be created
April 25, 2012 at 12:17 am
Why don't you keep an Index Table where you can store all the Customers and the names of the Databases of the respective Customers?
Then you can create a Stored Procedure to Select the respective Database names from the Index Table and pass it dynamically to whatever query you want to run.
April 25, 2012 at 12:35 am
i have heard using indexes have lot of problems . is it so...........
April 25, 2012 at 1:46 am
poornipmca (4/25/2012)
yes each customer will have different tables.But only 50 customers table will be there in that database .After that a new database will be created
Are the structures for the tables for each customer the same?
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply