Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question

2 Answers

up vote 3 down vote

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.

share|improve this answer
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? – chungtinhlakho Oct 2 '12 at 19:09
@chungtinhlakho - You haven't really explained your root problem. Just your proposed solution, which isn't possible, but probably something here will help. – Martin Smith Oct 2 '12 at 19:35
up vote 0 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.