how to write a function that executes Sub- Store procedure

Question

how to write a function that executes Sub- Store procedure

John Paul-702936

SSCrazy

Points: 2182
More actions
July 23, 2009 at 8:11 am

#156009

Hi ,
Can anyone help to write a dynamic function that executes Sub Store procedure.
here is the Scenario like :
I have store procedure sp1
which use #temp table and sp2
The sp2(sub store procedure)which inserts the data into #temp table
#temp(a,b)
like :
Create Proceudre sp1 (@a varchar, @b-2 varchar)
as
create#temp(@a,@b)
INSERT INTO #temp
Exec sp2 (@a,@b)
But , if i get a requirement that a extra columns need to be added for sp2 like sp2(@a,@b,@c,@d,@e)
this will indirectly effects the sp1(main store procedure)
which throws an error.
like Colums Mismatch between #temp and sp2
So , In the Place of EXEC sp2(@a,@b,@c,@d,@e), i want to use a function that Automatically detects the Columns and Which #temp table required , Executes the sub store procedure
So far , i tried to explain , i hope you people got some idea .
Can any one Help to create a function.

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Functions can't call stored procedures. Sorry, but you have to re-think what you want to do.

You can write a stored procedure that uses a function to generate a string variable containing dynamic SQL and then execute the dynamic SQL variable.

declare @sql

set @sql = dbo.ufWriteSomeSQL

exec sp_executeSQL @sql

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

Agreed. A function won't do. That said - if you were to replace the word function with "stored procedure", and you probably could do what you're looking at.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Also, just in case you are tempted to explain why it HAS to be a function and not a stored proc. Please, don't bother. I have had a couple of developers in the last year explain to me why it would be so much easier if functions could call stored procs, so they could keep logs of how often the function got called, etc. We understand, but the rule remains the same: Functions can't call stored procs.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

. (7/23/2009)
Also, just in case you are tempted to explain why it HAS to be a function and not a stored proc. Please, don't bother. I have had a couple of developers in the last year explain to me why it would be so much easier if functions could call stored procs, so they could keep logs of how often the function got called, etc. We understand, but the rule remains the same: Functions can't call stored procs.

Dunno about 2k5, but functions can call extended stored procedures if you care to take the time to build one.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I wasn't going to bring that up, because, despite the name, I don't consider extended stored procedures to be stored procedures in the sense the author of the question meant. They are calls to routines which are external to the database.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

. (7/24/2009)
I wasn't going to bring that up, because, despite the name, I don't consider extended stored procedures to be stored procedures in the sense the author of the question meant. They are calls to routines which are external to the database.

Spot on... I agree. Heh... they're as bad or worse than CLR's. 😛

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

Paul White SSC Guru Points: 150468 More actions · Answer 7

. (7/23/2009)
Functions can't call stored procedures.

Not strictly true - a CLR user-defined function can perform data access, including calling stored procedures.

The stored procedure code is subject to the same restrictions as functions though.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Hey Paul 🙂

Jeff already pointed out that it's not strictly correct, but I stand by my statement anyway. Just like string parsing or concatenation, if someone comes to an SQL forum and asks me how to do it in SQL, I am not going to start talking about writing regular expressions in C#. Call me irresponsible. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

Paul White (7/26/2009)
. (7/23/2009)
Functions can't call stored procedures.
Not strictly true - a CLR user-defined function can perform data access, including calling stored procedures.
The stored procedure code is subject to the same restrictions as functions though.
Paul

Correct - even if it's not enforced currently (meaning - I have managed to get away with updating data through CLR functions), it's not within the design, and would lead to various ugliness the minute someone decides to make sure that CLR follows the previously established rules of "no modification".

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

Paul White SSC Guru Points: 150468 More actions · Answer 10

. (7/27/2009)
Hey Paul 🙂
Jeff already pointed out that it's not strictly correct, but I stand by my statement anyway. Just like string parsing or concatenation, if someone comes to an SQL forum and asks me how to do it in SQL, I am not going to start talking about writing regular expressions in C#. Call me irresponsible. 😉

Yes - but Jeff was referring to extended stored procedures (xp_) not CLR routines.

It's certainly not irresponsible to exclude CLR, but it is a bit limiting.

CLR stuff is as much part of T-SQL now as anything else: look at the number of T-SQL commands and syntax extensions for it. Also consider the spatial stuff in 2008 and the fact that some other built-ins like sp_replcmds call MS-shipped CLR code.

Avoiding CLR for the sake of it is a bit like avoiding Service Broker, XML Web Services, or anything else that isn't SELECT, INSERT, UPDATE or DELETE - it's certainly possible, but kinda restricting.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 11

Matt Miller (7/27/2009)
Correct - even if it's not enforced currently (meaning - I have managed to get away with updating data through CLR functions), it's not within the design, and would lead to various ugliness the minute someone decides to make sure that CLR follows the previously established rules of "no modification".

I didn't know that data updates weren't enforced currently - thanks for that.

I couldn't agree more with the rest of what you say - and it's probably a bad idea to call a procedure from a CLR function anyway in most cases.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 12

[font="Verdana"]I think the original author to the question is using the word "function" in the generic "block of functional code" sense, rather than the SQL function sense. The examples all talk about stored procedures.

In which case, creating stored procedures that generate dynamic SQL to get around the issue is fine. Stored procedures can easily call other stored procedures.

Personally, I'd prefer to do this in SQL Server 2008, where you can use table types and table variables passed as parameters, as that makes the interface between the stored procedures explicit. However, in SQL Server 2005, this is pretty much the only way to do it.

[/font]

Paul White SSC Guru Points: 150468 More actions · Answer 13

Bruce W Cassidy (7/27/2009)
[font="Verdana"]I think the original author to the question is using the word "function" in the generic "block of functional code" sense, rather than the SQL function sense. The examples all talk about stored procedures.
Personally, I'd prefer to do this in SQL Server 2008, where you can use table types and table variables passed as parameters, as that makes the interface between the stored procedures explicit. However, in SQL Server 2005, this is pretty much the only way to do it.
[/font]

Nice catch Bruce! We all got a bit carried away with the functions there.

There is another way to do it in 2K5 using output cursor variables, but Jeff would kill me if I mentioned that :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

I notice the OP has been silent throughout this little discussion.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills