How to create a UDF that takes a query string and returns the query's resultset

Question

I want to create a stored procedure that takes a simple SELECT statement and return the resultset as a CSV string. So the basic idea is get the sql statement from user input, run it using EXEC(@stmt) and convert the resultset to text using cursors. However, as SQLServer doesn't allow:

• select * from storedprocedure(@sqlStmt)
• UDF with EXEC(@sqlStmt)

so I tried Insert into #tempTable EXEC(@sqlStmt), but this doesn't work (error = "invalid object name #tempTable").

I'm stuck. Could you please shed some light on this matter?

Many thanks

EDIT:

Actually the output (e.g CSV string) is not important. The problem is I don't know how to assign a cursor to the resultset returned by EXEC. SP and UDF do not work with Exec() while creating a temp table before inserting values is impossible without knowing the input statement.

I thought of OPENQUERY but it does not accept variables as its parameters.

Answer 1

You need to create the temp table with the same columns as what the stored proc returns before inserting into it.

However, outputting to CSV doesn't sound like a job particularly suited to SQL. Are you limited to doing it with a stored proc, or can you write a script or program to take the results and store in CSV? That would probably be an overall easier approach.

Answer 2

If I'm not mistaken you want a stored procedure that accepts a SELECT statment and returns the results, optionaly as a CSV.

So EXEC prExecute 'select * from tablea' is what you'd like to call? That's fairly east:

CREATE PROC prExecute ( @sql varchar(2000))
AS
    EXEC (@sql);
GO

But I don't understand the need for the cursor here.