locked
Dynamic Query Help RRS feed

  • Question

  • Experts,

    I've sample tables with sample records as:-

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Entity]') AND type in (N'U'))
    DROP TABLE [dbo].[Entity]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[EntityOnlyChanges]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
    DROP TABLE [dbo].[Address]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddressOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[AddressOnlyChanges]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Claims]') AND type in (N'U'))
    DROP TABLE [dbo].[Claims]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClaimsOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[ClaimsOnlyChanges]
    GO
    
    CREATE TABLE [dbo].[Entity]
    (
    	BatchID			INT,
    	ID				INT,
    	FNAME			VARCHAR(10),
    	LNAME			VARCHAR(10),
    	GENDER			VARCHAR(1),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    CREATE TABLE [dbo].[EntityOnlyChanges]
    (
    	BatchID			INT,
    	ID				INT,
    	FNAME			VARCHAR(10),
    	LNAME			VARCHAR(10),
    	GENDER			VARCHAR(1),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    INSERT INTO [dbo].[Entity] VALUES ( 1, 121, 'Mike', 'Ty', 'F', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 1, 222, 'Lance', 'May', 'M', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 1, 601, 'Will', 'Portz', 'M', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 1, 636, 'Abc', 'XYZ', 'M', '' )
    
    INSERT INTO [dbo].[Entity] VALUES ( 2, 121, 'Mike', 'Ty', 'F', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 2, 222, 'Lance', 'May', 'M', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 2, 601, 'Will', 'Portz', 'F', '' )
    INSERT INTO [dbo].[Entity] VALUES ( 2, 636, 'Abc', 'XYZ', 'M', '' )
    
    CREATE TABLE [dbo].[Address]
    (
    	BatchID			INT,
    	ID				INT,
    	AID				INT,
    	ADD1			VARCHAR(35),
    	ST				VARCHAR(2),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    INSERT INTO [dbo].[Address] VALUES ( 1, 121, 1210, '28th St', 'MO', '' )
    INSERT INTO [dbo].[Address] VALUES ( 1, 222, 2220, '56th St', 'IL', '' )
    INSERT INTO [dbo].[Address] VALUES ( 1, 222, 2221, '76 Bvld', 'IL', '' )
    INSERT INTO [dbo].[Address] VALUES ( 1, 601, 6010, '601 Main Rd', 'NJ', '' )
    INSERT INTO [dbo].[Address] VALUES ( 1, 636, 6360, '45th St', 'CA', '' )
    INSERT INTO [dbo].[Address] VALUES ( 1, 636, 6361, '5th St', 'CA', '' )
    
    INSERT INTO [dbo].[Address] VALUES ( 2, 121, 1210, '28th St', 'MO', '' )
    INSERT INTO [dbo].[Address] VALUES ( 2, 222, 2220, '56th St', 'IL', '' )
    INSERT INTO [dbo].[Address] VALUES ( 2, 222, 2221, '86 Bvld', 'IL', '' )
    INSERT INTO [dbo].[Address] VALUES ( 2, 601, 6010, '601 Main Rd', 'NJ', '' )
    INSERT INTO [dbo].[Address] VALUES ( 2, 636, 6360, '45th St', 'CA', '' )
    INSERT INTO [dbo].[Address] VALUES ( 2, 636, 6361, '6th St', 'CA', '' )
    
    CREATE TABLE [dbo].[AddressOnlyChanges]
    (
    	BatchID			INT,
    	ID				INT,
    	AID				INT,
    	ADD1			VARCHAR(35),
    	ST				VARCHAR(2),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    CREATE TABLE [dbo].[Claims]
    (
    	BatchID			INT,
    	ID				INT,
    	AID				INT,
    	Phone			VARCHAR(10),
    	Ext				VARCHAR(5),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    
    INSERT INTO [dbo].[Claims] VALUES ( 2, 121, 1210, '1234567890', '10095', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6360, '5420567890', '14298', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6361, '5558567890', '22232', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 1, 601, 6010, '5420151890', '22246', '' )
    
    INSERT INTO [dbo].[Claims] VALUES ( 2, 121, 1210, '1234567890', '15295', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6360, '5420567890', '14298', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 2, 636, 6361, '5558567890', '19532', '' )
    INSERT INTO [dbo].[Claims] VALUES ( 2, 601, 6010, '5420151890', '15846', '' )
    
    CREATE TABLE [dbo].[ClaimsOnlyChanges]
    (
    	BatchID			INT,
    	ID				INT,
    	AID				INT,
    	Phone			VARCHAR(10),
    	Ext				VARCHAR(5),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    SELECT	*
    FROM	[dbo].[Entity]
    ORDER BY	BatchID, ID
    
    SELECT	*
    FROM	[dbo].[Address]
    ORDER BY	BatchID, ID
    
    SELECT	*
    FROM	[dbo].[Claims]
    ORDER BY	BatchID, ID
    
    INSERT INTO [dbo].[EntityOnlyChanges] VALUES ( 2, 601, 'Will', 'Portz', 'F', 'C' )
    
    INSERT INTO [dbo].[AddressOnlyChanges] VALUES ( 2, 222, 2221, '86 Bvld', 'IL', 'C' )
    INSERT INTO [dbo].[AddressOnlyChanges] VALUES ( 2, 636, 6361, '6th St', 'CA', 'C' )
    
    INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 121, 1210, '1234567890', '15295', 'C' )
    INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 636, 6360, '5420567890', '14298', 'C' )
    INSERT INTO [dbo].[ClaimsOnlyChanges] VALUES ( 2, 636, 6361, '5558567890', '19532', 'C' )
    
    SELECT	*
    FROM	[dbo].[EntityOnlyChanges]
    ORDER BY	BatchID, ID
    
    SELECT	*
    FROM	[dbo].[AddressOnlyChanges]
    ORDER BY	BatchID, ID
    
    SELECT	*
    FROM	[dbo].[ClaimsOnlyChanges]
    ORDER BY	BatchID, ID
    
    EXEC [dbo].[procGetChanges_Temp1] 'Entity,Address, Claims ', 'ID', 'AID', 2
    
    SELECT	*
    FROM	[dbo].[EntityOnlyChanges]
    ORDER BY	CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
    
    SELECT	*
    FROM	[dbo].[AddressOnlyChanges]
    ORDER BY	CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
    
    SELECT	*
    FROM	[dbo].[ClaimsOnlyChanges]
    ORDER BY	CASE WHEN FUNCTION_FLAG = 'C' THEN 1 ELSE 2 END, ID
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Entity]') AND type in (N'U'))
    DROP TABLE [dbo].[Entity]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[EntityOnlyChanges]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
    DROP TABLE [dbo].[Address]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddressOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[AddressOnlyChanges]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Claims]') AND type in (N'U'))
    DROP TABLE [dbo].[Claims]
    GO
    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClaimsOnlyChanges]') AND type in (N'U'))
    DROP TABLE [dbo].[ClaimsOnlyChanges]
    GO

    Stored Procedure to get "Same" (S) values is here,

    CREATE PROCEDURE [dbo].[procGetChanges_Temp1]
    (
    	@TableNames							VARCHAR(MAX),
    	@PrimaryKeyColumn					VARCHAR(255),
    	@PrimaryKeyColumn1					VARCHAR(255),
    	@LatestIsReadyImportImportBatchID	INT
    ) AS
    BEGIN
    
    	DECLARE @TblName		VARCHAR(MAX)	=	@TableNames,
    			@Split			CHAR(1)			=	',',
    			@TblList		XML
    
    	SELECT	@TblList = CONVERT(XML,' <root> <s>' + REPLACE(@TblName,@Split,'</s> <s>') + '</s>   </root> ')
    
    	SELECT	ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ID,
    			[TablesList] = LTRIM(RTRIM(T.c.value('.','VARCHAR(30)')))
    	FROM	@TblList.nodes('/root/s') T(c)
    	
    	INSERT INTO [dbo].[EntityOnlyChanges] ( BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG )
    	SELECT	BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG
    	FROM
    			(
    				SELECT	BatchID, ID, FNAME, LNAME, GENDER, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Entity]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND ID IN
    							(
    								SELECT	ID
    								FROM	[dbo].[AddressOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND ID NOT IN
    											(
    												SELECT	ID
    												FROM	[dbo].[EntityOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    
    				UNION ALL
    
    				SELECT	BatchID, ID, FNAME, LNAME, GENDER, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Entity]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND ID IN
    							(
    								SELECT	ID
    								FROM	[dbo].[ClaimsOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND ID NOT IN
    											(
    												SELECT	ID
    												FROM	[dbo].[EntityOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    				) EntityOnlyChanges
    	GROUP BY	BatchID, ID, FNAME, LNAME, GENDER, FUNCTION_FLAG
    	ORDER BY	BatchID, ID
    
    	INSERT INTO [dbo].[AddressOnlyChanges] ( BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG )
    	SELECT	BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG
    	FROM
    			(
    				SELECT	BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Address]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND ID IN
    							(
    								SELECT	ID
    								FROM	[dbo].[EntityOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND ID NOT IN
    											(
    												SELECT	ID
    												FROM	[dbo].[AddressOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    
    				UNION ALL
    
    				SELECT	BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Address]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND AID IN
    							(
    								SELECT	AID
    								FROM	[dbo].[ClaimsOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND AID NOT IN
    											(
    												SELECT	AID
    												FROM	[dbo].[AddressOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    			) AddressOnlyChanges
    	GROUP BY	BatchID, ID, AID, ADD1, ST, FUNCTION_FLAG
    	ORDER BY	BatchID, ID
    
    	INSERT INTO [dbo].[ClaimsOnlyChanges] ( BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG )
    	SELECT	BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG
    	FROM
    			(
    				SELECT	BatchID, ID, AID, Phone, Ext, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Claims]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND ID IN
    							(
    								SELECT	ID
    								FROM	[dbo].[EntityOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND ID NOT IN
    											(
    												SELECT	ID
    												FROM	[dbo].[ClaimsOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    
    				UNION ALL
    
    				SELECT	BatchID, ID, AID, ADD1, ST, 'S' AS FUNCTION_FLAG
    				FROM	[dbo].[Address]
    				WHERE	BatchID = @LatestIsReadyImportImportBatchID
    							AND AID IN
    							(
    								SELECT	AID
    								FROM	[dbo].[AddressOnlyChanges]
    								WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											AND FUNCTION_FLAG = 'C'
    											AND AID NOT IN
    											(
    												SELECT	AID
    												FROM	[dbo].[ClaimsOnlyChanges]
    												WHERE	BatchID = @LatestIsReadyImportImportBatchID
    											)
    							)
    			) AddressOnlyChanges
    	GROUP BY	BatchID, ID, AID, Phone, Ext, FUNCTION_FLAG
    	ORDER BY	BatchID, ID
    
    END
    
    GO

    I'm able to get my expected result set with the above SP, but I want to make the above SP dynamic so that if number of tables get increase I don't have to modify the SP currently (above) SP is very much static with can handle only 3 tables.

    Can anybody please help me out?

    Please let me know if you have any questions for me.

    Note:-
    Entity table - One record per individual
    Address table - Each individual can have multiple address
    Claims table - If multiple address per individual multiple claims entries as well

    Thanks in advance

    Regards,

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Wednesday, May 28, 2014 5:35 PM

All replies

  • Thanks for posting the definitions and sample data!

    What are you trying to accomplish? this looks like a audit log of data changes?

    What about adding a trigger to [dbo].[Claims] that uses INSERTED or DELETED to insert records into [dbo].[ClaimsOnlyChanges]

    Wednesday, May 28, 2014 7:19 PM
  • I would be very cautious with trying to produce something that is "dynamic" with regards to tables. In a relational database, a table is supposed to model a unique entity with a number of unique attributes. From this perspective, it is unlikely that the same piece of code would be applicable to two tables execpt by chance.

    I did not fully look into what your procedures does, but having more than once tried to generalise code in my career, I have found that it is often non-trivial. It is fairly mechanical exercise to change the table names to parameters and then use dynamic SQL. But what says that the next set of tables has exactly these columns? Add to this that this procedure already is a bit complex. If you change the code to use dynamic SQL throughout, you increase the complexity further, and thereby you make it more difficult to maintain.

    I think that a better option if you anticipate to write a bunch of procedures of this type is to write program that generates the code. This permits you to make manual tweaks to the code that you are not able to include in your program.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 29, 2014 9:52 AM