What about this?
SELECT * FROM #bla
This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb
--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go
--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go
--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END
Now call the function a couple of times
SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)
SELECT * FROM LogMeNow
What if you were to run this?
SELECT dbo.fnBla(4),* FROM sys.sysobjects
See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!
I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(
SQL Server 2005 makes it pretty easy to do it yourself
SELECT
specific_name,*FROM information_schema.routines
WHERE
object_definition(object_id(specific_name)) LIKE '%somefuction%'AND
routine_type = 'procedure'
BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character
