CODEWITHSUNDEEP
sqlsql-server
Gericke
Gericke

Reputation: 2241

Send query as parameter to SQL function

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!

Upvotes: 5

Views: 9085

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

What I see in your question is encapsulation of:

  • taking a dynamic SQL expression
  • executing it to fill a parametrized table

Why do you want to have such an encapsulation?

First, this can have a negative impact on your database performance. Please read this on EXEC() and sp_executesql() . I hope your SP won't be called from multiple parts of your application, because this WILL get you into trouble, at least performance-wise.

Another thing is - how and where are you constructing your SQL? Obviously you do it somewhere else and it seems its manually created. If we're talking about a contemporary application, there are lot of OR/M solutions for this and manual construction of TSQL in runtime should be always avoided if possible. Not to mention EXEC is not guarding you against any form of SQL injection attacks. However, if all of this is a part of some database administration TSQL bundle, forget his paragraph.

At the end, if you want to simply load a new table from some existing table (or part of it) as a part of some administration task in TSQL, consider issuing a SELECT ... INTO ... This will create a new target table structure for you (omitting indexes and constraints) and copy the data. SELECT INTO will outperform INSERT INTO SELECT because SELECT INTO gets minimally logged.

I hope this will get you (and others) at least a bit on the right track.

Upvotes: 3

Hitesh
Hitesh

Reputation: 45

You can use stored procedure as well, here is the code that 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

Upvotes: -5

Devart
Devart

Reputation: 121912

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

Upvotes: 4

Related Questions