asked
Combine Multiple views and invoke it using Stored Procedure
Hello Techie, may anyone please share your expertise . i have a table contain customerid which is getting truncated each time before loading other set of data. i have multiple view created for each customer. i am trying to create a SP which can combine all the view, and execute the view based on the customerID available in the table. DDL AND views: -- Table CREATE TABLE CUSTOMER ( [CUSTOMERID] [int] NOT NULL ) INSERT CUSTOMER SELECT 100 UNION SELECT 105 UNION SELECT 108 ---- view 1 CREATE VIEW Customer1 AS WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY myid ORDER BY mydate) as RN FROM OUTPUT_table m WHERE CUSTOMERID = '100' ) SELECT c.*, ISNULL(DATEADD(second,-1,c2.mydate),'12/31/9999 12:00:00 AM') as TO_DATE FROM cte c LEFT JOIN cte c2 ON c2.RN = c.RN + 1 AND c.myid = c2.myid; GO ------ view 2 CREATE VIEW customer2 AS ( SELECT *, CASE WHEN MYCurrent = 1 THEN '9999-12-31' ELSE TO_DATE END AS TO_DATE, FROM OUTPUT_TABLE mt OUTER APPLY ( SELECT MAX(DATEADD(second, -1, mydate)) TO_DATE FROM OUTPUT_TABLE mt2 WHERE mt2.myid = mt.myid AND mt2.mydate > mt.mydate ) oa WHERE mt.CUSTOMERID = '105' ) GO Thanks
Why are you creating a different view for each customer? This is NOT the right way to go at all. Once you start creating copies of code over and over with nothing but a hard-coded value it is a sign you need to stop and find a different approach. Consider what would happen if you need to change this view and you have 10,000 customers. You have to change 10,000 views. This is a horrible direction to go. Perhaps if you could explain clearly what you are trying to do we can help. Unfortunately your views are querying a table we can't see. And from your post there is no point in the Customer table as it isn't referenced anywhere in this.
@seanlange: Thanks for suggestion. Please allow me to provide brief. Here i have implemented SCD-2 and loading the datamart using views from warehouse table. as data is not consistant across customer, i need to create multiple views as per requirement. different logic implemented for each for Individual customer. as you can see from example. each customer are identified by unique customerID. so i am trying to match the CustomerID in staging table and where clause of view, when they match the respective view should get selected. i am sure this is horrible..... kindly suggest.
A couple serious issues you have going on here too is the inclusion of select *. This is bad design, and in a view it will NOT do what you think it does. It will not dynamically change when the table definition changes. In fact, it can cause some serious problems down the road. If you drop a column and add another column and the datatypes are the same, the view will continue to function but the data in the columns can get moved around.
This is pretty simple with dynamic sql. It screams of horrible design but whatever, that is pain you are going to have to deal with. declare @CustomerID int = 105 declare @SQL nvarchar(MAX) select 'select * from Customer c join Customer' + CONVERT(varchar(4), @CustomerID) + ' v on v.CustomerID = c.CustomerID where c.CustomerID = ' + CONVERT(varchar(4), @CustomerID) from Customer where CustomerID = @CustomerID select @SQL --uncomment the following when you get the dynamic sql right --exec sp_executesql @SQL
20 People are following this question.
