locked
Dynamical column name RRS feed

  • Question

  • Hello,

    USE [Practice]
    GO
    
    IF OBJECT_ID('tempdb..#TableColName') IS NOT NULL
    	DROP TABLE #TableColName
    
    IF OBJECT_ID('tempdb..#Pro') IS NOT NULL
    	DROP TABLE #Pro
    
    CREATE TABLE #TableColName
    (
    	ID			INT IDENTITY(1,1) NOT NULL,
    	ColName		VARCHAR(40) NOT NULL
    )
    
    DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
    SET @str		=	'FNAME,GENDER'--'FNAME,DOB'
    SET @delimiter	=	','
    
    SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
    
    INSERT INTO #TableColName
    SELECT	N.value('.', 'varchar(10)') AS ColName
    FROM	@xml.nodes('X') as T(N)
    
    SELECT	*
    FROM	#TableColName
    
    IF OBJECT_ID('dbo.PROVIDER') IS NOT NULL
    	DROP TABLE [dbo].[PROVIDER]
    
    CREATE TABLE [dbo].[PROVIDER]
    (
    	ImportBatchID	INT	NOT NULL,
    	PID		INT NOT NULL,
    	FNAME	VARCHAR(10) NOT NULL,
    	DOB		DATETIME NULL,
    	GENDER	VARCHAR(1) NULL
    )
    
    INSERT INTO [dbo].[PROVIDER] VALUES ( 1 ,101, 'John', '1980-10-14', 'M' )
    INSERT INTO [dbo].[PROVIDER] VALUES ( 1 ,110, 'Nancy', '1975-03-04', 'F' )
    INSERT INTO [dbo].[PROVIDER] VALUES ( 1 ,115, 'Smith', '1970-05-24', 'M' )
    
    INSERT INTO [dbo].[PROVIDER] VALUES ( 2 ,101, 'John', '1980-10-14', 'M' )
    INSERT INTO [dbo].[PROVIDER] VALUES ( 2 ,110, 'Nancy', '1985-03-04', 'F' )
    INSERT INTO [dbo].[PROVIDER] VALUES ( 2 ,115, 'Smith', '1970-05-24', '' )
    
    CREATE TABLE #Pro
    (
    	PID	INT,
    	FNAME	VARCHAR(1),
    	DOB	DATETIME,
    	GENDER	VARCHAR(1),
    	FUNCTION_FLAG	VARCHAR(1)
    )
    
    DECLARE
    	@TableName						VARCHAR(255)	=	'PROVIDER',
    	@LatestIsReadyImportBatchID		INT				=	2,
    	@PreviousIsReadyImportBatchID	INT				=	1,
    	@PrimaryKeyColumn				VARCHAR(255)	=	'PID'
    
    DECLARE	@TblName					VARCHAR(255)	=	@TableName,
    		@LatIsReadyImportBatchID	INT				=	@LatestIsReadyImportBatchID,
    		@PrvIsReadyImportBatchID	INT				=	@PreviousIsReadyImportBatchID,
    		@PriKeyColumn				VARCHAR(255)	=	@PrimaryKeyColumn
    
    DECLARE	@ColumnList			NVARCHAR(MAX)	=	'';
    
    SELECT	@ColumnList = @ColumnList + 'ISNULL([' + c.[name] + '], '''') AS [' + c.[name] + '],' + CHAR(10)
    FROM	sys.tables t
    		INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
    WHERE	t.[name] = @TableName
    			AND c.name IN ( SELECT ColName FROM #TableColName )
    ORDER BY	c.column_id;
    
    --SELECT	@ColumnList
    
    DECLARE @Sql NVARCHAR(MAX) = '';
    
    SET @Sql = 
    
    	'SELECT *' + CHAR(10)
    	+ 'FROM ' + CHAR(10)
    	+ '(' + CHAR(10)
    
    	+ 'SELECT ' + CHAR(10)
    	+ CAST(@LatIsReadyImportBatchID AS NVARCHAR(MAX)) + ' AS [ImportBatchID], ' + CHAR(10)
    	+ @ColumnList
    	+ '''C'' AS FUNCTION_FLAG' + CHAR(10)
    	+ 'FROM dbo.[' + REPLACE(@TblName, ']', ']]') + '] c1' + CHAR(10)
    	+ 'WHERE c1.ImportBatchID = ' + CAST(@LatIsReadyImportBatchID AS NVARCHAR(MAX)) + CHAR(10)
    	+ 'AND EXISTS (' + CHAR(10)
    	+ 'SELECT *' + CHAR(10)
    	+ 'FROM dbo.[' + REPLACE(@TblName, ']', ']]') + '] c2' + CHAR(10)
    	+ 'WHERE c2.ImportBatchID = ' + CAST(@PrvIsReadyImportBatchID AS NVARCHAR(MAX)) + CHAR(10)
    	+ 'AND c2.[' + REPLACE(@PriKeyColumn, ']', ']]') + '] = c1.[' + REPLACE(@PriKeyColumn, ']', ']]') + '])' + CHAR(10)
    		
    	+ 'EXCEPT' + CHAR(10)
    		
    	-- exclude records that are across the board exact matches with previous batch
    	+ 'SELECT ' + CHAR(10)
    	+ CAST(@LatIsReadyImportBatchID AS NVARCHAR(MAX)) + ' AS [ImportBatchID], ' + CHAR(10)
    	+ @ColumnList
    	+ '''C'' AS FUNCTION_FLAG' + CHAR(10)
    	+ 'FROM dbo.[' + REPLACE(@TblName, ']', ']]') + '] c1' + CHAR(10)
    	+ 'WHERE c1.ImportBatchID = ' + CAST(@PrvIsReadyImportBatchID AS NVARCHAR(MAX)) + CHAR(10)--+ ';'
    
    	+ ') abc' + CHAR(10)
    
    PRINT	@Sql
    --SELECT	@Sql
    
    --INSERT INTO #Pro--Need to insert into #Pro table if columns are coming from below exec statement insert those values else null
    EXEC sp_executesql @Sql;
    
    SELECT	*
    FROM	#Pro

    Based on what columns are passed into @str variable ( 'FNAME,GENDER'--'FNAME,DOB' ) it displays the changes, obviously if I uncomment above INSERT statement query won't execute since number of columns aren't matching

    Need help in dynamical column names to insert into #Pro table.

    Thanks

    Regards,

    gk03


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

    Wednesday, June 15, 2016 7:09 PM

Answers

  • But then how can I access #Pro table outside of sp_exeuctesql like this?

    EXEC sp_executesql @Sql;
    
    SELECT	*
    FROM	#Pro


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

    • Marked as answer by gk1393 Friday, June 17, 2016 2:55 AM
    Wednesday, June 15, 2016 7:26 PM

All replies

  • Can you perform the insert inside the proc? This then becomes a simple SELECT * INTO #pro FROM..

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, June 15, 2016 7:22 PM
  • But then how can I access #Pro table outside of sp_exeuctesql like this?

    EXEC sp_executesql @Sql;
    
    SELECT	*
    FROM	#Pro


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

    • Marked as answer by gk1393 Friday, June 17, 2016 2:55 AM
    Wednesday, June 15, 2016 7:26 PM
  • By not using a temp table.

    Simply create, use and drop the table as part of your process.

    When I have the occasion to do things like this, I'll often create and use a schema named tmp, and append the spid of the process to the table name. You can then pass this back from the proc, so the rest of your process can use it.

    This means, of course, you're going to be stuck using dSQL, but I feel that's going to be in your future anyway :)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Wednesday, June 15, 2016 8:52 PM