Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Return result of dynamic query from function Expand / Collapse
Author
Message
Posted Thursday, October 25, 2007 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:44 PM
Points: 7,439, Visits: 17,495
rose_red1947 (10/24/2007)
Try with SET clause before @RESULT = Execute @SQLQuery


No....

Let's try this again. from BOL:


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.

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.



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #415000
Posted Thursday, October 1, 2015 8:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 9, 2015 10:10 AM
Points: 235, Visits: 82
Its because, semicolon missing at the end of return statement.
Use the simplified one below :

CREATE FUNCTION dbo.ExecuteStringAsQuery (@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
/* Build Transact-SQL String with parameter value */
return(select JoinDateQuery from NewHireEmployee where empid= + @empID);
END
Post #1724648
Posted Friday, October 2, 2015 5:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 40,992, Visits: 38,289
vinod.andani-874416 (10/1/2015)
Its because, semicolon missing at the end of return statement.
Use the simplified one below :

CREATE FUNCTION dbo.ExecuteStringAsQuery (@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
/* Build Transact-SQL String with parameter value */
return(select JoinDateQuery from NewHireEmployee where empid= + @empID);
END


Not likely. There is currently no requirement in SQL to require a RETURN to be followed by a semi-colon unless the next statement is something like a CTE which wouldn't make sense. The real problem was that folks were trying to execute dynamic SQL from within a function.

Only because it matters to some people, I'll also point out that this post is 8 years old.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1724910
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse