Send query as parameter to SQL function

Question

I want to create an SQL tabled-value function that will receive a query as an n-parameter through my API. In my function, I want to fulfill this request. The query will be a SELECT statement.

This is what I have done so far and what needs to be done, but this is not the right way to do it.

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 right way!

Answer 1

Try this option -

 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 

Answer 2

What I see in your question is encapsulation:

• with dynamic SQL expression
• execute it to populate a parameterized table

Why do you want to have such encapsulation?

First of all, this can adversely affect the performance of your database. Read this in EXEC () and sp_executesql () . I hope that your SP will not be called from several parts of your application, because it will lead to trouble, at least to performance.

Another thing is how and where do you build your SQL? Obviously, you are doing this somewhere else, and it seems that it was created manually. If we are talking about a modern application, there are many OR / M solutions for this, and whenever possible you should always avoid manually assembling TSQL at run time. Not to mention that EXEC does not protect you from any form of SQL injection attack. However, if all of this is part of the TSQL database administration package, forget its paragraph.

In the end, if you just want to load a new table from any existing table (or part of it) as part of some administration task in TSQL, consider releasing SELECT ... INTO ... This will create a new target table structure for you ( excluding indexes and restrictions) and copy the data. SELECT INTO will outperform INSERT INTO SELECT because SELECT INTO will be minimally registered .

Hope this gets you (and others) at least a little on the right track.

Answer 3

You can also use a stored procedure, here is the code you can try.

 CREATE FUNCTION CUSTOM_EXPORT_RESULTS ( @query varchar(max), @guid uniqueidentifier, @tableName varchar(200) ) RETURNS TABLE AS RETURN ( declare @strQuery nvarchar(max) -- Execute query into a table SET @strQuery = REPLACE(@query,'FROM', 'INTO ' +@tableName +' FROM') exec sp_executesql @strQuery ) GO