May 16, 2012 at 11:30 pm
Can I pass a parameter Dynamically to Open Query inside a Function like this?
Create Function dbo.Fun_Proc_Test1(@id1 int)
Returns
varchar(30)
As
Begin
Declare @res varchar(30)
Set @res = (Select * From OpenQuery(ABHI, 'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = '+Cast(@id1 As Varchar(5))+', @chng_name = @result OUTPUT Select @result') )
Return @res
End
I don't think I am doing it right. When I execute the above code.....I get the following error:
Msg 102, Level 15, State 1, Procedure Fun_Proc_Test1, Line 7
Incorrect syntax near '+'.
What is the correct way of passing parameters Dynamically to Open Query in a Function?
Thanks,
May 17, 2012 at 3:32 am
Try it
Create Function dbo.Fun_Proc_Test1(@id1 int) Returns varchar(30)
As
Begin
Declare @res varchar(30)
--Set @res = (Select * From OpenQuery(ABHI, N'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ''', @chng_name = @result OUTPUT Select @result') )
Declare @Tmp varchar(100) = 'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ', @chng_name = @result OUTPUT Select @result'
Select @res = N'(Select * From OpenQuery(ABHI, @Tmp))'
Return @res
End
May 17, 2012 at 10:30 pm
No, Preetham.
It doesn't work. It will just return the value of @res which in this case is "(Select * From OpenQuery(ABHI, @Tmp))". It won't execute the Open Query. I actually read about it in Books Online and found out that Open Query does not accept variable input.
So, we can execute a Stored Procedure from a Function but cannot pass input to it Dynamically.
Referance: Open Query
If someone has any other ideas then they are more than welcome.
Thanks,
May 17, 2012 at 10:35 pm
What is the original requirement?
May 17, 2012 at 10:59 pm
ColdCoffee (5/17/2012)
What is the original requirement?
I was trying to Execute a Stored Procedure from inside a Function. This was not actually a Business Requirement. Just for learning purposes and to see how far I can go with this.
So, I created a Stored procedure with one input parameter and one out parameter. Then I executed it inside the function.
This worked fine when I was passing a Static input to the Stored Proc. But, when I tried passing the input dynamically using a temp variable, it started giving Error.
May 17, 2012 at 11:01 pm
preetham gowda (5/17/2012)
Try it
Create Function dbo.Fun_Proc_Test1(@id1 int) Returns varchar(30)
As
Begin
Declare @res varchar(30)
--Set @res = (Select * From OpenQuery(ABHI, N'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ''', @chng_name = @result OUTPUT Select @result') )
Declare @Tmp varchar(100) = 'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ', @chng_name = @result OUTPUT Select @result'
Select @res = N'(Select * From OpenQuery(ABHI, @Tmp))'
Return @res
End
Actually you can only call extended stored procedures from a function. From BOL:
The following statements are valid in a function:
Assignment statements.
Control-of-Flow statements except TRY...CATCH statements.
DECLARE statements defining local data variables and local cursors.
SELECT statements that contain select lists with expressions that assign values to local variables.
Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements modifying local table variables.
EXECUTE statements calling extended stored procedures.
For more information, see Create User-defined Functions (Database Engine).
I recall a thread where we ascertained there are no "sanctioned" methods for calling any form of dynamic SQL from a function (unless you build the function in SQLCLR, which may allow you to "cheat" somewhat on criteria).
----------------------------------------------------------------------------------
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?
May 17, 2012 at 11:24 pm
Matt Miller (#4) (5/17/2012)
preetham gowda (5/17/2012)
Try it
Create Function dbo.Fun_Proc_Test1(@id1 int) Returns varchar(30)
As
Begin
Declare @res varchar(30)
--Set @res = (Select * From OpenQuery(ABHI, N'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ''', @chng_name = @result OUTPUT Select @result') )
Declare @Tmp varchar(100) = 'Declare @result varchar(30) Exec Abhi.Test.dbo.convert2Lower @id = ' + Cast(@id1 As Varchar(5)) + ', @chng_name = @result OUTPUT Select @result'
Select @res = N'(Select * From OpenQuery(ABHI, @Tmp))'
Return @res
End
Actually you can only call extended stored procedures from a function. From BOL:
The following statements are valid in a function:
Assignment statements.
Control-of-Flow statements except TRY...CATCH statements.
DECLARE statements defining local data variables and local cursors.
SELECT statements that contain select lists with expressions that assign values to local variables.
Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements modifying local table variables.
EXECUTE statements calling extended stored procedures.
For more information, see Create User-defined Functions (Database Engine).
I recall a thread where we ascertained there are no "sanctioned" methods for calling any form of dynamic SQL from a function (unless you build the function in SQLCLR, which may allow you to "cheat" somewhat on criteria).
Yes Matt, you are right. I might not be able to Execute anything Dynamic in the Function.(I knew this before....but I didnt understand the exact meaning of this...the exact meaning is: "You can't execute ANYTHING that is Dynamic From inside a Function").:-D
But, the part where you said that you can only Execute Extended Stored procedures is not completely true.
You can execute Stored Procedures(which accept a static input) from within the Function using Open Query. I just tried it and it works. 🙂
May 18, 2012 at 8:07 am
The list of "allowed constructs" is out of Books Online, and is very specific about what can and cannot be called within a function. It's intended to be an exhaustive list of those thing you can do within a function. Regular stored procedures is not on the list, and exec <proc> as well as sp_eecuteSQL <proc> are both blocked by the compiler.
As while you are correct that the compiler currenly isn't smart enough to detect the stored proc call, you are going against the intended use. So - it's more along the lines of "don't be surprised when this function stops working", simply because a new compiler can now detect the "non-allowed construct". As it stands I have already seen the compiler get a bit more stringent about detecting changes made within a function using CLR (another "cheat" method), so again - don't rely on this method to get around the restriction.
----------------------------------------------------------------------------------
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?
May 18, 2012 at 5:47 pm
Matt Miller (#4) (5/18/2012)
As while you are correct that the compiler currenly isn't smart enough to detect the stored proc call, you are going against the intended use.
Vinu for President! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2012 at 7:46 pm
Jeff Moden (5/18/2012)
Matt Miller (#4) (5/18/2012)
As while you are correct that the compiler currenly isn't smart enough to detect the stored proc call, you are going against the intended use.Vinu for President! 😀
Don't disagree with the sentiment. The choices they made about functions were too limiting. Would have been a REALLY nice way to implement something like parameterized queries (something that cleanly returns datasets and can be called from a SELECT x from... syntax).
Just warning about possible landmines:)
----------------------------------------------------------------------------------
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?
May 18, 2012 at 10:24 pm
I completely agree with you, Matt. You are right about the limitations of a Function and the problems one might run into by using such techniques.
This was just an attempt to walk on the "less traveled" path and getting to know how far it goes. It was just a learning attempt.
Vinu for President!
Woohoo!!!...:-D:hehe:
Well...on second thought I'd rather have it this way.....
Vinu for next Jeff Moden!!! ;-):-P
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply