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.