Execute Stored Procedure from a Function

sql sql-server sql-server-2005 stored-procedures user-defined-functions
Smur picture Smur · Jun 14, 2011 · Viewed 135.1k times · Source

I know this has been asked to death, and I know why SQL Server doesn't let you do it.

But is there any workaround for this, other than using Extended Stored Procedures?

And please don't tell me to convert my function into a procedure...

So what I'm really asking is: Is there ANY way to run a Stored Procedure from within a Function?

EDIT:

Point proven: there is a way around it, but it's so WRONG I wouldn't do it. I'm gonna change it to a Stored Procedure and execute it elsewhere.

Answer

Tom Chantler picture Tom Chantler · Jun 14, 2011

EDIT: I haven't tried this, so I can't vouch for it! And you already know you shouldn't be doing this, so please don't do it. BUT...

Try looking here: http://sqlblog.com/blogs/denis_gobo/archive/2008/05/08/6703.aspx

The key bit is this bit which I have attempted to tweak for your purposes:

DECLARE @SQL varchar(500)

SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec dbName..sprocName "'

EXEC master..xp_cmdshell @SQL

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More
SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' …

Read More