CODEWITHSUNDEEP
sqlsql-servert-sqlsql-function
chungtinhlakho
chungtinhlakho

Reputation: 930

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

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

Upvotes: 1

Views: 6567

Answers (2)

TyT
TyT

Reputation: 299

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

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.

Upvotes: 3

Related Questions