SQL Function takes an sql string, execute the string and returns the table

Question

I've done a lot of functions that return a table but somehow this one keeps on giving me an error at Begin and I couldn't figure out for the life of me why. My where statement is stored in a table. This function I want to pass in a value and the where statement.

CREATE FUNCTION dbo.Testtesttest(@employeeID        AS INT,
                                 @sqlWhereStatement AS VARCHAR(max))
Returns TABLE
  BEGIN
      DECLARE @mySQLStatement VARCHAR(max)

      SET @mySQLStatement = 'Set Quoted_Identifier OFF Select '
                           + CONVERT(VARCHAR, @employeeID) + ',* from (
        select m.ManagerID, m.Name,m.Email,e.BU,
        e.PSC from m inner join e on m.ManagerID = e.EmployeeID
        group by m.ManagerID, m.Name,m.Email,e.BU,e.SC,
        e.PSC) x where ' + @sqlWhereStatement

      EXEC(@mySQLStatement)

      RETURN
  END

Martin Smith · Accepted Answer · 2012-10-02 18:22:06Z

3

You can't use dynamic SQL in a function so what you are trying to do is not possible via a TSQL TVF.

Maybe a CLR UDF can do something like this but I would find a different approach anyway.

answered Oct 2, 2012 at 18:22

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chungtinhlakho Over a year ago

if have i have function returning a complete sql string, can I do this exec(myFunctionThatReturnSQLString) but sadly this won't work as well. :( any thoughts?

Martin Smith Over a year ago

@chungtinhlakho - You haven't really explained your root problem. Just your proposed solution, which isn't possible, but probably something here will help.

TyT · Accepted Answer · 2012-10-02 19:31:28Z

0

Why don't you just wrap that logic in a stored procedure? If you need to take the results of sproc into a table, you can use INSERT INTO EXEC

INSERT INTO
yourTable(col1, col2, col3)
EXEC Testtesttest @employeeID, @sqlWhereStatement

answered Oct 2, 2012 at 19:31

TyT

2992 silver badges5 bronze badges

Collectives™ on Stack Overflow

SQL Function takes an sql string, execute the string and returns the table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related