restrict result set of table variable

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

General SQL Server Forums

New to SQL Server Programming

restrict result set of table variable

Author

Topic

sqldoubt
Starting Member

17 Posts

Posted - 2010-06-09 : 00:28:39

I am getting columns updated and pushing it into a temporary table variable. Now while retreiving those columns from that table variable i want to restrict two columns called date and audit. How can achieve this. Can restrict those two columns while getting all updated columns using COLUMNS_UPDATED itself???

SET @ColumnsUpdated = COLUMNS_UPDATED()
//can i restrict those 2cols here in COLUMNS_UPDATED
DECLARE @MEMTABLE TABLE
(ID INT IDENTITY,
COLUMNAME SYSNAME,
TYPENAME VARCHAR(20) )

INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('[DBO].[OrderItem]')
ORDER BY COLID
SET @SQLSTR = ''
SET @CUR=1
SELECT @MAX = MAX(ID) FROM @MEMTABLE

WHILE @CUR <= @MAX
BEGIN
select @CURCOL=COLUMNAME from @MEMTABLE where ID=@CUR
// or can i do it here in this select

if exists(
SELECT COLUMN_NAME AS Name FROM INFORMATION_SCHEMA.COLUMNS Field
WHERE TABLE_NAME = 'OrderItem' AND sys.fn_IsBitSetInBitmask
(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0 and column_name=@CURCOL)
Begin

print @CURCOL+' column modifed'

insert into OrderItem_Audit (columnName, createdDate) values (@CURCOL ,getdate())

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-09 : 01:13:40

Put a if statement
if(COLUMNS_UPDATED()<>date or COLUMNS_UPDATED()<>audit)
SET @ColumnsUpdated = COLUMNS_UPDATED()

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources