4 Replies - 1270 Views - Last Post: 18 January 2016 - 11:06 PM

#1 h4nnib4l   User is offline

  • The Noid
  • member icon

Reputation: 1274
  • View blog
  • Posts: 1,808
  • Joined: 24-August 11

Trying to call scalar-valued function within stored procedure

Posted 07 January 2016 - 06:05 PM

In order to facilitate some additional automation in an application we maintain, we've decided that all equipment numbers will have a delimiter between each tag component (PART1-PART2-PART3-SUFFIX) when we import new equipment. Now I need to go through the database and update the existing equipment numbers on any active or recently-finished projects to include those delimiters, so that their tags match the nomenclature of the new tags.

I wrote a scalar-valued function (udf_GetDelimitedEquipmentNumber) that will take a given project ID and equipment ID, use that information to rebuild the equipment number with delimiters based on look-ups to other tables, and then return the equipment number. Works perfectly.

Now, I'm writing a stored procedure that can be called to update all of the equipment numbers in an entire project. From that stored proc, I would like to call udf_GetDelimitedEquipmentNumber to get the new equipment number to apply for each record; however, when I call it, I get the error Only functions and some extended stored procedures can be executed from within a function.

I just want to know if there's any combination of stored procedures and functions that will allow me to do this, or if I'm going to have to copy/paste the guts of that function into the main stored procedure. None of the examples/answers that I've found will explicitly answer that for me, and after creating 2 different functions and a stored procedure to try to call this, I would like to know if it's futile before I try anything else.

Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Trying to call scalar-valued function within stored procedure

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14088
  • View blog
  • Posts: 56,440
  • Joined: 12-June 08

Re: Trying to call scalar-valued function within stored procedure

Posted 07 January 2016 - 07:54 PM

Why a scalar function and not another stored procedure?
Was This Post Helpful? 0
  • +
  • -

#3 h4nnib4l   User is offline

  • The Noid
  • member icon

Reputation: 1274
  • View blog
  • Posts: 1,808
  • Joined: 24-August 11

Re: Trying to call scalar-valued function within stored procedure

Posted 07 January 2016 - 09:15 PM

No practical reason really; it seemed to better-match the organization of functionality already implemented, so I tried that approach first. I'm the only one maintaining the application though, so I don't think anyone will mind too much. I'll give the stored procedure route a whirl and report back.

I guess more than anything, I was just really surprised that I couldn't run that function from within the stored proc. I assumed that's how functions were designed to be used. Granted, I'm a C# dev by trade and pseudo-DBA by necessity, so I guess I tend to approach T-SQL a bit like dirty object oriented code with too much junk in Main...
Was This Post Helpful? 0
  • +
  • -

#4 h4nnib4l   User is offline

  • The Noid
  • member icon

Reputation: 1274
  • View blog
  • Posts: 1,808
  • Joined: 24-August 11

Re: Trying to call scalar-valued function within stored procedure

Posted 18 January 2016 - 03:01 PM

FWIW, I ended up building it into a stored procedure with an OUTPUT parameter and it works fine. I'd still be interested if anyone knows why I couldn't call that scalar function from my main stored procedure though.
Was This Post Helpful? 0
  • +
  • -

#5 BBeck   User is offline

  • Here to help.
  • member icon


Reputation: 792
  • View blog
  • Posts: 1,886
  • Joined: 24-April 12

Re: Trying to call scalar-valued function within stored procedure

Posted 18 January 2016 - 11:06 PM

The error appears to be that you cannot call stored procedures inside of functions.

Stored Procedures in SQL Server are actually functions inspite of the name. They return values but they don't necessarily have to (which would make them procedures) and they can return tables and such with more than one value which strictly speaking makes them not functions.

I came from the Pascal world where a function is a function and a procedure is a procedure and never shall the twain meet. Since then, I've switched languages so many times I just kind of go with the flow. I appreciate the rigor but there's just some things in the world you can't change. Whether they call it a function, a procedure, a method, a stored procedure, or whatever it's all good. I think the mathematical definition of a function is the problem because functions should be bidirectional and have multiple inputs and outputs even in mathematics which would make some mathematicians heads explode. Part of the problem with mathematics is due to the definition of the function. Square roots being a primary example; in the real world there are four answers, but because of the definition of the function you only have 1 and you have to cheat and say + or - the given answer to show there are two answers. There are actually four but half of them are identical and so there are only 2 unique answers.

Anyway, my point there is that I've learned to just accept what ever definition of a "function" a given programming language wants to go with and go with it.

I've never used functions much in SQL Server, but I've always had the impression they are there only for extremely light weight functions like a line or two of code mostly for mathematical purposes. I also have never heard much talk of them being optimized in the query optimizer.

It could be because Stored Procedures break the rules for functions. Specifically - as an example, you're not allowed to make DML statements such as INSERT, UPDATE, and DELETE in a function that modify table variables. It would be hard to enforce that if you could just call a Stored Proc in the middle of the function to do the DML for you. Anyway, there appears to be some very strict rules around how functions can be used and how they can carry out their goals which Stored Procs don't have to adhere to. So, perhaps it would be difficult or impossible to keep functions within those specific parameters if they were allowed to call code that violates those parameters.

This post has been edited by BBeck: 18 January 2016 - 11:11 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1