InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 13:57:37 GMT20http://www.sqlservercentral.com/Forums/Topic413342-145-1.aspx[quote][b]rose_red1947 (10/24/2007)[/b][hr]Try with SET clause before @RESULT = Execute @SQLQuery[/quote]No....Let's try this again. from BOL:[quote]The types of statements that are valid in a function include: DECLARE statements can be used to define data variables and cursors that are local to the function.Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.Control-of-flow statements except TRY...CATCH statements.SELECT statements containing select lists with expressions that assign values to variables that are local to the function.UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.EXECUTE statements calling an extended stored procedure.[/quote]Notice the last line - you can only call Extended stored procs using exec. Dynamic SQL is not allowed.And lest you ask: sp_executeSQL is not an extended stored proc, so it's not legal either. Thu, 25 Oct 2007 08:48:29 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxAssuming that, the column [b]JoinDateQuery[/b] will only have two values i.e. "(select hiredate from employeeInfo)" or "(select getdate)" then you can define a bit column [b]HasHireDate [/b] instead of [b]JoinDateQuery[/b], with this you can define a simple static query like this....SELECT H.EmpID, COALESCE( E.HireDate, GETDATE() ) FROM NewHireEmployee H LEFT JOIN EmployeeInfo E ON H.EmpID = E.EmpID AND H.HasHireDate = 1 WHERE H.EmpID = @iEmpID Thu, 25 Oct 2007 04:47:29 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxHi there, i'm not sure what you really want, but my random guess is that you are trying to execute dynamic sql, and you wanted the results to be returned as a database table. Obviously the function you have created will only return you a string as you have specified "RETURNS Varchar(8000)". If you want the function to return a database table then you'll have to use table value function as suggested by Grant Fritchey. But you'll need to define the table you want to return.why not just use a stored procedure?It can execute dynamic sql + returns results as a result setCREATE PROCEDURE [dbo].[spName](@empID AS NVARCHAR(500))ASBEGIN SET NOCOUNT ON; SET @SQL = 'select JoinDateQuery from NewHireEmployee where empid=' + @empID EXEC(@SQL)ENDRETURNORWhere @SQL = 'select JoinDateQuery from NewHireEmployee where empid=001'or@SQL = 'select getdate()'CREATE PROCEDURE [dbo].[spName](@SQL AS VARCHAR(8000))ASBEGIN SET NOCOUNT ON; EXEC(@SQL)ENDRETURNBut be caution about using dynamic sql ;)cheers :)Thu, 25 Oct 2007 04:24:15 GMTjon-474332http://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxTry with SET clause before @RESULT = Execute @SQLQueryWed, 24 Oct 2007 15:42:24 GMTrose_red1947http://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxGenerically - you cannot use EXECUTE or sp_executeSQL within a function. You can only call functions or extended stored procs from within a function, and those ain't it.Good news is - you don't need either for what you're doing. You don't need dynamic SQL at all for that matter.Try this instead:[code]CREATE FUNCTION dbo.GetJoinDate(@empID as nvarchar(500))RETURNS Varchar(8000)ASBEGINDECLARE @RESULT AS NVARCHAR(500)Select @result=JoinDateQuery from NewHireEmployee where empid= @empID return @RESULTEND[/code]Tue, 23 Oct 2007 13:07:40 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxLook at sp_executesqlDECLARE @ReturnValue DATETIME, @EmployeeID INTSET @EmployeeID = 1exec sp_executesql N'SELECT @JoinDate = JoinDateQuery NewHireEmployee where empid = @EmpID ', N'@EmpID int, @JoinDate DATETIME OUTPUT', @EmpID = @EmployeeID, @JoinDate = @ReturnValue OUTPUTSELECT @ReturnValue Tue, 23 Oct 2007 02:00:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxYou're trying to combine the function of EXEC where it executes an ad hoc sql string with the function of exec where it captures the return status of the execution of a query. They don't go together. Further, you're trying to capture the output of the procedure into a string. That won't work either. Instead, you need to make this a table valued function and simply execute the ad hoc sql string.Mon, 22 Oct 2007 06:59:33 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxSorry, you got me on this one. :unsure:Mon, 22 Oct 2007 06:08:22 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxHi,still it gives error :(Incorrect syntax near '@RESULT'.I want the result of query stored in database table.. I'm succeeded to fetch the qeury. But it is as string.I'm not sure of how to trigger a string to be executed as sql query & return resultset.Mon, 22 Oct 2007 06:00:03 GMTchinmayeehttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxYou need to encapsulate the SQL text in single quotes as below.[code]CREATE FUNCTION dbo.ExecuteStringAsQuery(@empID as nvarchar(500))RETURNS Varchar(8000)ASBEGINDECLARE @SQLQuery AS NVARCHAR(500),@RESULT AS NVARCHAR(500)/* Build Transact-SQL String with parameter value */SET @SQLQuery = '(select JoinDateQuery from NewHireEmployee where empid=' + @empID + ');'@RESULT = Execute @SQLQueryreturn @RESULTEND[/code]Mon, 22 Oct 2007 05:34:49 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic413342-145-1.aspxHi,In my application, i'm storing sql query as value of table field in database. Like in my [b]NewHireEmployee[/b] table, value of JoinDateQuery field can be (select hiredate from employeeInfo) or simply (select getdate)I want to fetch this query, evaluate it and then return the result of query. I tried to write a function like this,CREATE FUNCTION dbo.ExecuteStringAsQuery(@empID as nvarchar(500))RETURNS Varchar(8000)ASBEGINDECLARE @SQLQuery AS NVARCHAR(500),@RESULT AS NVARCHAR(500)/* Build Transact-SQL String with parameter value */SET @SQLQuery = (select JoinDateQuery from NewHireEmployee where empid= + @empID)@RESULT = Execute @SQLQueryreturn @RESULTENDBut it is giving error like 'Line 10: Incorrect syntax near '@RESULT'.I'm using sql server 2000Any ideas? Thanks in advanced!Mon, 22 Oct 2007 04:25:39 GMTchinmayee