How can call stored procedure inside function in sql server 2005

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

Not possible

Thanks,
Sanjay

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

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

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

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

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

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

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

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