question

Peter001 avatar image
Peter001 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
sql-server-2008views
16 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
I'll echo @seanlange in that this design is going to be (if not already) a nightmare to manage. Why are the two views different? Are you reporting differently per customer?
0 Likes 0 ·
@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.
0 Likes 0 ·
@JohnM: Hi John, actually there are 30 customer and for each customer views created with different logic. :) my bad... Please share your suggestion.
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
seanlange avatar image
seanlange answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Sean
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.