UDF Issue, Just Need A Bit Of Clarity
Oct 5, 2007Given this simple sproc:-
Create proc spGetData
as
select 1 as PrettyDeterministicMeThinks
you cannot do this:
create function fnGetData()
returns @table table (col1 varchar(10))
as
begin
insert into @table exec spGetData --This is my function_body
return
end --you get an "Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function" error
but you CAN do this
create function fnGetData()
returns @table table (col1 varchar(10))
as
begin
insert into @table select 1 as PrettyDeterministicMeThinks --This is my function_bodyreturn
end
I just wanted to know what the difference was as books online refers to the function body as "a series of Transact-SQL statements that populate a TABLE return variable." which is what I thought I was doing in either case. So my question was really how is this wrong
THX