Defeated by Table Valued Function

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 2000 Forums

SQL Server Development (2000)

Defeated by Table Valued Function

Author

Topic

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-10-09 : 20:28:15

After much head-pounding I must admit defeat. Can anyone explain to me why the following code works if I pass a hard coded value to the function (which returns a single col result set):

...AND c.colid IN (SELECT * from master.dbo.fn_HexToColList(0x02))

But it doesn't work when I pass it a col value:

...AND c.colid IN (SELECT * from master.dbo.fn_HexToColList(p.columns))

SELECT o.name AS [Table Name], c.name, p.action AS [Permission], p.protecttype AS [Protect Type],p.columns AS [Columns],u2.name AS [Grantor]
    FROM sysusers u 
    JOIN sysprotects p
    ON u.uid = p.uid
    JOIN sysusers u2
    ON u2.uid = p.grantor
    JOIN sysobjects o
    ON p.id = o.id
    JOIN syscolumns c
    ON o.id = c.id
    AND c.colid IN (SELECT * from master.dbo.fn_HexToColList(p.columns))
    AND u.name = 'Management'
    AND p.columns IS NOT NULL
    ORDER BY [Table Name],[Permission]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources