How to create a UDF that takes a query string and returns the query's resultset
- by Martin
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.
