Using EXEC in Query

  • Thread starter Thread starter Steven Livingstone
  • Start date Start date
S

Steven Livingstone

How do i call "exec" from within a select statement :

e.g. something like this is what i want....

SELECT id, ChildCount = EXEC GetKids id
FROM mytable

Should be a no brainer, but i seem to be missing the exact syntax and can't
find any examples.

thanks,
Steven.
 
Assuming SQL Server, to do this use a scalar UDF (user defined function)
instead.

Brad Williams
 
Yeah - that was what i wanted to do.
But it is a recursive call and uses a temporary table to store the results
throughout the recursion. Temp tables apparently cannot be used in UDF's -
but they can in SP's. Equally you cannot call an SP from a UDF.

Any further ideas on this?
 
You probably won't like this, but you could write this as your SQL command:

Set NoCount Off
Declare @tTemp Table (id int, kids int)
Declare @id int, @kids int
Declare curID cursor local fast_forward for
select id from MyTable
open curID
fetch next from curID into @id
while @@fetch_status=0 Begin
Exec @kids = GetKids id
Insert @tTemp(id, kids) values (@id, @kids)
fetch next from curID into @id
end
close curID
deallocate curID
Select id, kids from @tTemp

(note that line breaks are completely optional in T-SQL, so you could put
this all in a one-line string constant if you wanted)

Alternately, you could take all of the above, throw it in a stored
procedure, and call that.
 
Back
Top