Send query as parameter to SQL function

sql, sql-server

Solution

Try this one -

CREATE PROCEDURE dbo.sp_CUSTOM_EXPORT_RESULTS

       @query NVARCHAR(MAX) = 'SELECT * FROM dbo.test'
     , @guid UNIQUEIDENTIFIER
     , @tableName VARCHAR(200) = 'test2'

AS BEGIN

     SELECT @query = 
          REPLACE(@query, 
               'FROM', 
               'INTO [' + @tableName + '] FROM')

     DECLARE @SQL NVARCHAR(MAX)
     SELECT @SQL = '
     IF OBJECT_ID (N''' + @tableName + ''') IS NOT NULL
          DROP TABLE [' + @tableName + ']
     ' + @query

     PRINT @SQL
     EXEC sys.sp_executesql @SQL

     RETURN 0

END
GO

Output -

IF OBJECT_ID (N'test2') IS NOT NULL
     DROP TABLE [test2]
SELECT * INTO [test2] FROM dbo.test

Problem

I want to create a SQL tabled-value function that will receive a query as n parameter through my API. In my function I want execute that query. The query will be a SELECT statement. This is what I have done so far and what to achieve but it is not the correct way to do so. ``` CREATE FUNCTION CUSTOM_EXPORT_RESULTS ( @query varchar(max), @guid uniqueidentifier, @tableName varchar(200)) RETURNS TABLE AS RETURN ( -- Execute query into a table SELECT * INTO @tableName FROM ( EXEC(@query) ) ) GO ``` Please suggest the correct way!

Original source