I have a stored proc that takes parameters from an ASP page and using a cursors, creates a dynamic crosstab query. I would like to turn this dynamic crosstab query into a function that returns a table. I've searched the internet and found numerous examples, but I keep getting syntax errors. I build the SELECT statement as a string.
Your CREATE FUNCTION statement looks like Inline Function which should start with RETURN SELECT ... after AS. But the contents of the function ... You realize you can't use EXECUTE in there, right? But, you can use OPENROWSET. Maybe, if you write a stored procedure that does the transformation and then do SELECT * from OPENROWSET(...) within your function?
I don't think your inline function can take a string variable as a select statement. Have you seen examples of this on the internet? I'd be interested in seeing them if you have. I can't find any in SQL Server documentation.
This is from Books On Line:
The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
If you are trying to pass a variable to the return statement, it has no way to verify that it meets the restrictions or anticipate the result format.
If you have a defined set of column headers, then you should ultimately be able to find a way to get the results you want. Otherwise you are limited to what you can jam into a single pre-defined select statement.
Good eye, bm! Well, for this we will probably need to use a parameter table that will acquire the values of the passed parameters. This way we will not have to do anything with parameters passed other than storing them to the parameter table. We also need to convert the function from inline to table-valued to accomplish this.