October 22, 2007 at 4:25 am
Hi,
In my application, i'm storing sql query as value of table field in database.
Like in my NewHireEmployee 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)
AS
BEGIN
DECLARE @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 @sqlquery
return @RESULT
END
But it is giving error like
'Line 10: Incorrect syntax near '@RESULT'.
I'm using sql server 2000
Any ideas?
Thanks in advanced!
October 22, 2007 at 5:34 am
You need to encapsulate the SQL text in single quotes as below.
CREATE FUNCTION dbo.ExecuteStringAsQuery
(@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @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 @sqlquery
return @RESULT
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 22, 2007 at 6:00 am
Hi,
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.
October 22, 2007 at 6:08 am
Sorry, you got me on this one. :unsure:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 22, 2007 at 6:59 am
You'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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 2:00 am
Look at sp_executesql
DECLARE @ReturnValue DATETIME, @EmployeeID INT
SET @EmployeeID = 1
exec sp_executesql N'SELECT @JoinDate = JoinDateQuery NewHireEmployee where empid = @EmpID ',
N'@EmpID int, @JoinDate DATETIME OUTPUT', @EmpID = @EmployeeID, @JoinDate = @ReturnValue OUTPUT
SELECT @ReturnValue
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2007 at 1:07 pm
Generically - 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:
CREATE FUNCTION dbo.GetJoinDate(@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @RESULT AS NVARCHAR(500)
Select @result=JoinDateQuery from NewHireEmployee where empid= @empID
return @RESULT
END
----------------------------------------------------------------------------------
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?
October 25, 2007 at 4:24 am
Hi 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 set
CREATE PROCEDURE [dbo].[spName]
(@empID AS NVARCHAR(500))
AS
BEGIN
SET NOCOUNT ON;
SET @sql = 'select JoinDateQuery from NewHireEmployee where empid=' + @empID
EXEC(@SQL)
END
RETURN
OR
Where @sql = 'select JoinDateQuery from NewHireEmployee where empid=001'
or
@sql = 'select getdate()'
CREATE PROCEDURE [dbo].[spName]
(@SQL AS VARCHAR(8000))
AS
BEGIN
SET NOCOUNT ON;
EXEC(@SQL)
END
RETURN
But be caution about using dynamic sql 😉
cheers 🙂
October 25, 2007 at 4:47 am
Assuming that, the column JoinDateQuery will only have two values i.e. "(select hiredate from employeeInfo)" or "(select getdate)" then you can define a bit column HasHireDate instead of JoinDateQuery, 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
--Ramesh
October 25, 2007 at 8:48 am
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?
October 1, 2015 at 8:18 am
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
October 2, 2015 at 5:49 am
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
Change is inevitable... Change for the better is not.
March 24, 2020 at 11:51 am
You can't do that.
If you want to, you can:
SELECT @Result = a.SomeColumn
FROM dbo.ATable AS a
WHEREa.id=42;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply