Can we call stored Procedure inside a function

Question

Can we call stored Procedure inside a function

$t@r

SSCommitted

Points: 1751
More actions
June 12, 2013 at 3:57 am

#276213

Hi,
Can we call stored Procedure inside a function
Thanks

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

$t@r SSCommitted Points: 1751 More actions · Answer 2

One Guy Said yes We can.... and given an example

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...

$t@r SSCommitted Points: 1751 More actions · Answer 3

and he also given that we need to configure below options

Sp_Configure 'show advanced options',1

Reconfigure with Override

Go

Sp_Configure 'Ad Hoc Distributed Queries',1

Reconfigure with Override

Go

yes...I have tried it...it is working fine.......can anyone explain me what is happening here?????

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

$w@t (6/12/2013)
can anyone explain me what is happening here?????

OPENROWSET (Transact-SQL)

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.

OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

$w@t (6/12/2013)
Hi,
Can we call stored Procedure inside a function
Thanks

Why did you ask this question, when you already believed you knew the answer?

Would you consider implementing the method shown in your convoluted example in a production environment? What problem are you trying to solve?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

$t@r SSCommitted Points: 1751 More actions · Answer 6

After Posting my question only I have seen that solution...

I am not going to use it anywhere..I faced this Question in interview..so i posted here

I Just want to know Whether it is possible or not

Phil Parkin SSC Guru Points: 246695 More actions · Answer 7

$w@t (6/12/2013)
After Posting my question only I have seen that solution...
I am not going to use it anywhere..I faced this Question in interview..so i posted here
I Just want to know Whether it is possible or not

Ahh, I see. If you'd said 'yes' in the interview, and backed it up with that example, I think you might have got the job 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Personally, I hate using OPENROWSET in production. Any time a username and password has to be hard coded means that you're screwed if you ever change the password. Or you have to try to maintain a list of everywhere you hard code login credintials and the list becomes obsolete and you end up chasing them down anyway.

I like Gail's answer of "No" myself. Just because we can do something doesn't mean we should. The technical workaround does work, but I don't think it's a good idea.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Phil Parkin SSC Guru Points: 246695 More actions · Answer 9

I never said it was a good idea - Gail's answer would have been mine too.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

$t@r SSCommitted Points: 1751 More actions · Answer 10

$t@r

SSCommitted

Points: 1751

June 12, 2013 at 6:35 am

#1623307

Thank u Ed Wagner

Ed Wagner SSC Guru Points: 287024 More actions · Answer 11

I apologize if I got on a soap box there. I've been burned by that in the past and don't want others to have to suffer the same misery without know it's coming.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Ed Wagner (6/12/2013)
Just because we can do something doesn't mean we should. The technical workaround does work, but I don't think it's a good idea.

It's not a good idea.

The rules of a function (no side effects, no procedures) are so that the optimiser/execution engine is free to choose how many times the function will be executed without any concern about what could break if the function is called multiple times. When you do the openrowset hack, if anything you call has side effects (changes data, changes structure, creates files, etc) could create incorrect, inconsistent results or even errors, depending what happens behind the scenes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287024 More actions · Answer 13

Thank you, Gail. I hadn't even considered side effects of what's done in the OPENROWSET. Yet another reason not to use it to get around the built-in limitations. They actually exist for a reason. 😎

Thanks again.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

GilaMonster (6/12/2013)
Ed Wagner (6/12/2013)
Just because we can do something doesn't mean we should. The technical workaround does work, but I don't think it's a good idea.
It's not a good idea.
The rules of a function (no side effects, no procedures) are so that the optimiser/execution engine is free to choose how many times the function will be executed without any concern about what could break if the function is called multiple times. When you do the openrowset hack, if anything you call has side effects (changes data, changes structure, creates files, etc) could create incorrect, inconsistent results or even errors, depending what happens behind the scenes.

Agreed - based on Gail's explanation, this also has potential to be "fixed" at any time by the SQL dev team. As in - they can decide at any time to shut down that type of call within a function, and don't even need to explain why since it violates the stated intent of a function. I know they did that with a few things in the SQLCLR pieces a few releases back.

----------------------------------------------------------------------------------
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?