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.

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 23, 2009 at 10:28 am

  #1029124

  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

  July 23, 2009 at 10:33 am

  #1029129

  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

  July 23, 2009 at 10:36 am

  #1029137

  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: 1004318

  More actions

  July 23, 2009 at 7:08 pm

  #1029403

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

  July 24, 2009 at 7:55 am

  #1029682

  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: 1004318

  More actions

  July 24, 2009 at 8:14 am

  #1029699

  . (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: 150467

  More actions

  July 26, 2009 at 1:22 am

  #1030252

  . (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
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 27, 2009 at 2:31 pm

  #1030745

  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

  July 27, 2009 at 2:39 pm

  #1030747

  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: 150467

  More actions

  July 27, 2009 at 7:20 pm

  #1030799

  . (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
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150467

  More actions

  July 27, 2009 at 7:39 pm

  #1030808

  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
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Bruce W Cassidy

  SSChampion

  Points: 12668

  More actions

  July 27, 2009 at 7:51 pm

  #1030809

  [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: 150467

  More actions

  July 27, 2009 at 8:37 pm

  #1030817

  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
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 29, 2009 at 3:37 pm

  #1032046

  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