Need to exclude certain columns, using dynamic sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need to exclude certain columns, using dynamic sql

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-10-08 : 17:20:01

[code]I need to exclude certain columns, dynamic queries below does not work. Please see desire results below. SQL 2012

Thank you very much.

DROP TABLE dbo.TestEmp
GO

CREATE TABLE dbo.TestEmp
(
emp_id INT IDENTITY(1,1) NOT NULL CONSTRAINT XPKTestEmp PRIMARY KEY
,last_name VARCHAR(20) NULL
,first_name VARCHAR(15) NULL
,salary MONEY NULL
,email VARCHAR(80) NULL
,srvcode CHAR(5) NULL
);
GO

SELECT *
FROM TestEmp;
go

DECLARE @Capture_Col_List VARCHAR(8000) = '',
@cap_col_list VARCHAR(8000),
@sqlCommand VARCHAR(MAX),
@SchemaName VARCHAR(50) = 'dbo',
@TableName VARCHAR(50) = 'TestEmp' --'dbo_TaskDocument'
,@colName VARCHAR(8000) = 'salary, email, srvcode'

SELECT @cap_col_list = SUBSTRING( (SELECT ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (Table_Name = @TableName)
AND COLUMN_NAME NOT IN ( @colName )
FOR XML PATH( '') ), 2, 8000)
PRINT @cap_col_list

--results want:
emp_id last_name first_name[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-10-08 : 19:33:35

"Need to exclude certain columns, using dynamic sql"... Need to? What are you actually trying to accomplish; not how do you want to do it?

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 00:05:45

where's the dynamic sql?

also


SELECT @cap_col_list = SUBSTRING( (SELECT ', ' + COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS
        WHERE (Table_Name =  @TableName)
          AND COLUMN_NAME NOT IN ( @colName )
          FOR XML PATH( '') ), 2, 8000)

should be below i guess


SELECT @cap_col_list = SUBSTRING( (SELECT ', ' + COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS
        WHERE (Table_Name =  @TableName)
          AND ',' + @colName + ',' LIKE '%,' + COLUMN_NAME + ',%'
          FOR XML PATH( '') ), 2, 8000)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tm
Posting Yak Master

160 Posts

Posted - 2013-10-09 : 10:22:25

Another option would be to create a function (refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033) and create the dbo.fnParseList function by SwePeso.

Remove the spaces and modify the select ..

DECLARE @Capture_Col_List VARCHAR(8000) = '',
@cap_col_list VARCHAR(8000),
@sqlCommand VARCHAR(MAX),
@SchemaName VARCHAR(50) = 'dbo',
@TableName VARCHAR(50) = 'TestEmp' --'dbo_TaskDocument'
-- ,@colName VARCHAR(8000) = 'salary, email, srvcode'
, @colName VARCHAR(50) = 'salary,email,srvcode' -- NOTE: Remove Spaces here or change function to remove space <<<<<<<

SELECT @cap_col_list = SUBSTRING( (SELECT ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (Table_Name = @TableName)
-- AND COLUMN_NAME NOT IN ( @colName )
AND COLUMN_NAME NOT IN (SELECT data from dbo.fnParseList(',', @colName))
FOR XML PATH( '') ), 2, 8000)

PRINT @cap_col_list

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources