Hi experts and thanks for any help,
I would like to create a random Table View based on parameters passed.
I am new to all this.
I have 3 tables * etc....
tables all ARE NOT the same structure - FACILITY does not have File_Id
And yes there is a group for each month So there could be (12 * 3 = 36) Tables, depending on how many times the purge is run could even be more or less.
The View name = CLAIM_PURGE
I pass a parameter from SSIS
The new SP I am going to create, this is the reason for this post?
This parameter is based on a SQLStatement that checks for matching File_Ids
Where a condition causes the parameters. For this post there is only ONE parameter "TBL_NMS".
I have seen something close to what I am looking for (note this is just an example and this post is going to be even more dynamic):
SELECT @SQL1 = 'CREATE VIEW dbo.CLAIM_PURGE AS' + CHAR(10)
+ STUFF( -- Gets rid of first UNION ALL
( --=== Finds the correct table names and concatenates the
-- the necessary syntax for each table name to build
-- view using a "blank" untagged XML Path
SELECT 'UNION ALL SELECT '
--===== Conditionally drop the view
IF OBJECT_ID('dbo.CLAIM_PURGE ','V') IS NOT NULL
DROP VIEW dbo.CLAIM_PURGE
--===== Create the view using the generated syntax
--===== Not required, but show the syntax we just executed
Please help and thanks