Table user defined functions in 16 SP02

84 Views

I have created a function and would like to join to another table. Following the approach shown in the reference manual I am getting a stack trace if I select more than one row from the other table.

Here is a simplified example using system tables so it should be easy to try out anywhere:

if object_id('GetColumns') is not NULL drop function dbo.GetColumns

go

CREATE function dbo.GetColumns(@ObjId int)

returns

@cols table(ColumnName varchar(999))

as

begin

insert @cols(ColumnName)

select name from syscolumns where id = @ObjId

end

go

select * from dbo.GetColumns(1)

select * from dbo.GetColumns(NULL)

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 2

select o.id, o.name, c.ColumnName from sysobjects o, dbo.GetColumns(o.id) c where o.id = 1 or o.id = 2

The first four selects work fine, but the last one fails spectacularly with a stack trace.

Is this a known problem or am I using it incorrectly?

Thanks

Mark