Can we call stored Procedure inside a function

Question

Can we call stored Procedure inside a function

Viewing 7 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

Ed Wagner (6/12/2013)
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.

I agree that hardcoded credentials suck but you don't need to use hardcoded credentials with OPENROWSET. Trusted connections work just fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

Matt Miller (#4) (6/12/2013)
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.

I gave up living in that type of fear because the same holds true even for well documented functionality with little or no warning. I don't care if anyone else used it or not but I was going great guns with a wonderful little tool called sp_MakeWebtask. They decided to change the privs to use from whatever it was (I forget) to "SA" for "security reasons". Since the company I was working for had no clue how to allow a stored proc to run as "SA" without giving "SA" to the users that used it, that caused a huge problem. Very fortunately, there's more than one way to skin the proverbial cat but the nearly overnight change did cause a whole lot of work. It was essential that we installed the Service Pack for other reasons. Talk about a rock and a hard spot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mahavidhya24 SSC Veteran Points: 204 More actions · Answer 3

Thats not possible to call store procedure inside the function,we can call function with in the store procedure

mahavidhya24 SSC Veteran Points: 204 More actions · Answer 4

That not possible ,we can call function with in the store procedure.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

mahavidhya24 (6/12/2013)
Thats not possible to call store procedure inside the function,we can call function with in the store procedure

Oddly enough, they just proved that it was possible if you use OPENROWSET. Go back through this thread and have a look.

Considering that you can't use dynamic SQL in a function and that OPENROWSET takes only literals, I don't see an advantage, though. As Gail and some of the others have pointed out, it's also contrary to design and unsupported.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Jeff Moden (6/13/2013)
mahavidhya24 (6/12/2013)
Thats not possible to call store procedure inside the function,we can call function with in the store procedure
Oddly enough, they just proved that it was possible if you use OPENROWSET. Go back through this thread and have a look.
Considering that you can't use dynamic SQL in a function and that OPENROWSET takes only literals, I don't see an advantage, though. As Gail and some of the others have pointed out, it's also contrary to design and unsupported.

And probably dangerous if you consider the side effects of what gets called. As Gail pointed out, this could very well result in inaccurate results, so that makes it dangerous to use.

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Ed Wagner (6/13/2013)
Jeff Moden (6/13/2013)
mahavidhya24 (6/12/2013)
Thats not possible to call store procedure inside the function,we can call function with in the store procedure
Oddly enough, they just proved that it was possible if you use OPENROWSET. Go back through this thread and have a look.
Considering that you can't use dynamic SQL in a function and that OPENROWSET takes only literals, I don't see an advantage, though. As Gail and some of the others have pointed out, it's also contrary to design and unsupported.
And probably dangerous if you consider the side effects of what gets called. As Gail pointed out, this could very well result in inaccurate results, so that makes it dangerous to use.

If it wasn't used to call a proc to update anything, just select, that might temper the "dangeous-to-use" label a bit but dangerous or not and considering the other restrictions of functions and the fact that OPENROWSET only takes literals in a non-dynamic implementation, I don't currently see the benefit of ever using it except to give some interviewer an oolie to impress/depress a candidate with. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)