How can call stored procedure inside function in sql server 2005

Posted by Sekar.C under Sql Server on 4/5/2012 | Points: 10 | Views : 174332 | Status : [Member] | Replies : 9
Hi,
How can call stored procedure inside function in sql server 2005, its passable
Regards
Sekar.c

Regards
Sekar.c



Responses

Posted by: Pandians on: 4/5/2012 [Member] [MVP] Silver | Points: 25

Up
1
Down
YES we can..... :)

1. Creating a Stored Procedure:
If Object_Id('Proc1') Is Not Null

Drop Proc Proc1
Go
Create Proc Proc1
As
Begin
Select 'SQL Server'
End
Go
2. Creating a Function:
If Object_Id('Function1') Is Not Null

Drop Function Function1
Go
Create Function dbo.Function1() Returns @Result Table
(
Result Varchar(100)
)
As
Begin
Insert @Result
SELECT * from OPENROWSET('SQLNCLI10', 'Server=<SERVERNAME>;UID=<LOGIN>;Pwd=<PASSWORD>;',
'Exec dbo.Proc1') AS C
Return
end
Go
3. Executing the Function:
Select * from dbo.Function1()

Go
4. Result
Result

----------
SQL Server

- I have created one Stored procedure ("Proc1")
- I have created one Function. Calling the Stored procedure inside the Function using OPENROWSET :)
- I use the function to execute/call the stored procedure...

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 4/5/2012 [Member] Silver | Points: 25

Up
0
Down
Not possible

Thanks,
Sanjay

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 4/5/2012 [Member] Starter | Points: 25

Up
0
Down
We cannot call store procedure within a function. However, we can call a function within a store procedure.
Our teacher said so and he was arogantlly very good. I would love to if someone proov me wrong thos will give me a point to talk to him.

Cheer
nerd

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manjuhp on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down
If I try to compile the function I am getting this error


SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.


Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 7/5/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Configure the Option in your server!

Execute the Statement
Sp_Configure 'show advanced options',1

Reconfigure with Override
Go
Sp_Configure 'Ad Hoc Distributed Queries',1
Reconfigure with Override
Go



Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vinothmes on: 8/27/2012 [Member] Starter | Points: 25

Up
0
Down
i tried the 2 query for creating function but i got error

Go

Create Function dbo.Function1() Returns @Result Table
(
Result Varchar(100)
)
As
Begin
Insert @Result
SELECT * from OPENROWSET('SQLNCLI', 'Server=172.27.16.113;UID=sa1;Pwd=sa1;',
'Exec dbo.Proc1') AS C
Return
end
Go




I m still getting error as





Msg 7357, Level 16, State 2, Procedure Function1, Line 7
Cannot process the object "Exec dbo.Proc1". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 8/27/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Oh. It may be the Metatag can not be resolved!

Try this!

Step #1
Create Function dbo.Function1() Returns @Result Table 

(
Result Varchar(100)
)
As
Begin
Insert @Result
SELECT * from OPENROWSET('SQLNCLI', 'Server=172.27.16.113;UID=sa1;Pwd=sa1;',
'SET FMTONLY OFF; Exec dbo.Proc1') AS C
Return
end
Go


Still, If its same error then try to execute the following statement alone

Step #2
SELECT * from OPENROWSET('SQLNCLI', 'Server=172.27.16.113;UID=sa1;Pwd=sa1;', 

'SET FMTONLY OFF; Exec dbo.Proc1')
Is it working fine now ?

Still, Its throwing the same Error then, Try to find out what are all the providers you have in your server?

Step #3
Use Master

Go
EXEC dbo.xp_enum_oledb_providers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vinothmes on: 8/27/2012 [Member] Starter | Points: 25

Up
0
Down
i tried step 2 i got the following error

Msg 7357, Level 16, State 2, Line 2
Cannot process the object "SET FMTONLY OFF; Exec dbo.Proc1". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


i used step 3 and have attached the list of providers in my system ,i m using sql server 2005,does this is the reason for error

Awaiting your response


Regards
Vinoth
 Download source file

Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Surya5489 on: 12/20/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

I want to pass a argument to the procedure that is called inside a function. How can it be done?
I want to pass the argument value dynamically to the procedure when I run the function.
So I declared a parameter in the function as,

CREATE FUNCTION [dbo].[Proc1](@PARAM INT)

And then passed that argument name @PARAM in the 'OPENROWSET' as shown below:

SELECT * from OPENROWSET('SQLNCLI10', 'Server=<SERVERNAME>;UID=<LOGIN>;Pwd=<PASSWORD>;',

'Exec dbo.Proc1 @PARAM') AS C

But this does not work. Can anyone please let me know how we can pass parameters too when we call a procedure in a function.


Sekar.C, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response