April 27, 2012 at 8:15 am
Finally i got the answer
-- [USP_TEST] '2,3,4,17','Validated_Data','Validated_Data','BPO','222'
alter proc [USP_TEST]
(
@StateId nvarchar(100),
@h nvarchar(100),
@E nvarchar(100),
@d nvarchar(100),
@id nvarchar(10)
)
as
begin
declare @sql nvarchar(4000)
declare @sql1 nvarchar(1000)
declare @sql2 nvarchar(4000)
declare @dbs table(dbname sysname)
-- drop function dbo.fn_tot_alldbs
set @sql ='
create function [dbo].[UFN_GET_STATEWISE_CUSTOMER_COUNT_'+@id+'](
@StateId1 int,
@h nvarchar(100),
@d nvarchar(100)
)
returns int
as
--auto generated function code
begin
declare @intCount int
select @intcount=isnull(Count(*),0)
from '+@d+'..'+@h+' c
inner join '+@d+'..Dealer d on d.Dealer_ID=c.RefDealerID
inner join '+@d+'..State s on s.StateID=d.Ref_State_ID
where c.IsInactive=0 and c.IsReturn=0 and c.status=0 and StateID = (@StateId1)
return @intcount
end'
exec sp_executesql @sql
set @sql2='select distinct s.Statedesc,s.StateID ,CustCount=dbo.[UFN_GET_STATEWISE_CUSTOMER_COUNT_'+@id+'] (s.StateID,+'''+@h+''','''+@d+''')
from '+@d+'..'+@E + ' c
inner join '+@d+'..Dealer d on d.Dealer_ID=c.RefDealerID
inner join '+@d+'..State s on s.StateID=d.Ref_State_ID
where StateID in ('+(@StateId)+')'
print @sql
exec sp_executesql @sql2
print @sql2
set @sql1='drop function [UFN_GET_STATEWISE_CUSTOMER_COUNT_'+@id+']'
exec (@sql1)
end
April 27, 2012 at 8:25 am
:w00t: yikes! Are you just writing this code and are done with the project, or do you also have to support the system after it is in production? Rethink, redesign!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 11:17 pm
what problems will i face if i follow this method
April 27, 2012 at 11:20 pm
can any one help me out to choose with method will be suitable for multiple databases.
1. Dynamic sp
or
2.clr functions.
April 27, 2012 at 11:32 pm
poornipmca (4/27/2012)
can any one help me out to choose with method will be suitable for multiple databases.1. Dynamic sp
or
2.clr functions.
You really need to rethink your design. You are going to have more problems with it as you grow.
April 28, 2012 at 12:11 am
can u help me by telling what type of problems i may face by choosing any of this method
CLR functions
dynamic functions
April 28, 2012 at 4:04 am
poornipmca (4/28/2012)
can u help me by telling what type of problems i may face by choosing any of this methodCLR functions
dynamic functions
Every customer gets their own set of tables in a database, correct?
When the number of customers hits a predefined level, you create a new database and start adding new customers there.
You want a single set of procedures and functions that can access any database/set of tables. This design is going to be very difficult to maintain and enhance over time and really needs to be rethought, and redesigned.
April 28, 2012 at 4:40 am
yes.
But wat type of design u suggest.
can u suggest me wat problems i may face if i use this method
April 28, 2012 at 8:17 pm
poornipmca (4/28/2012)
yes.But wat type of design u suggest.
We already gave you options
- move to a multi-tenant database and have the application pass in the correct "customer id" to the database as needed based on the logged in customer
or - have the application change database context and call the correct objects based on the customer
i.e. give up trying to turn your database server into an application server and handle that responsibility in the application layer
can u suggest me wat problems i may face if i use this method
- difficult to implement initially
- difficult to maintain (most of your software dev costs)
- difficult to test and debug (related to precious two items)
- poor performance due to many possible reasons
- cache bloat
- wasteful use of CPU compiling objects for all requests
- having DDL in code creates security and concurrency concerns
Those are just a few high-level areas of concern, I am sure we could come up with more, from what you have described and the code you have shown.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply