User Defined Functions in SQL statements

Posted on 1998-08-05
Last Modified: 2010-03-19
I want to know whether a user defined functions can be used inside a SQL statement.  Suggest a solution as how to give a user defined function inside a SQL statement.

Question by:vvg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1089405
When you say 'user defined function' do you mean a stored procedure?

If I'm understanding your question, you cannot call a stored proc from inside a SQL statement.

select * from (exec (sp_stored_proc))


select * from <table> where <name> in (exec(stored_proc))

or any other variation will not work.

The idea is to put everything you will be needing into your stored proc and then calling it by itself.

Now what you *can* do is to call a stored proc from within a stored proc.  Declare variables in your first proc that you pass to your second proc.  In the second proc, make those parameters OUTPUT params.


create procedure sp1 as
  @var1 int,
  @var2 int

select @var1 = id1 from table1
select @var2 = id2 from table2

exec sp2 @var1, @var2

select * from mytable where id1 = @var1 and id2 = @var2


create procedure sp2 @outvar1 int OUTPUT, @outvar2 int OUTPUT as

if @outvar1 < @outvar2 then select @outvar2 = 10    --or whatever.


I hope this is helpful!  :)


Author Comment

ID: 1089406
Hi! Don, I think u haven't understood my question, also I'm sorry for not giving a detailed question. Here's my requirement,

I have an SQL in Access which is like this:

Select * from table1 as a, table1 as b where
(timespan(a.date1,a.date2,b.date1,b.date2) = true) and ....

Here timespan is a user-defined function in Access which accepts 4 dates are parameters and does some checking and returns true or false.

In SQL Server we can create a stored proc for this which will return true or false but the problem will be executing this stored proc inside the SQL ( ie using it in the Where clause).

So, how to go about it.

Thanks for u'r suggestion and hopefully if u could give the solution for the above specified problem, it will be of great help to  me.



Expert Comment

ID: 1089407
It would help if you gave an example of what the timespan function does . . .

One possible solution would be to write timespan as a separate stored procedure, call it from the first (with parameters if needed) and check the return code. This is what mayhew suggested. I know it's not quite the same as a function call, but it could be close enough, depending . . .


Accepted Solution

mitek earned 30 total points
ID: 1089408
There seems to be no way (i know of) to call a stored procedure from within a SELECT query in MS SQL Server (in Oracle, it IS possible though)

When I had to do something similar, I used code like the one below. Not pretty, but worked. Of course, it would be best to get rid of the function and try to figure out a way to do the timespan check in SQL query w/o SP. But if you ABSOLUTELY need to use an SP (like, if the procedure is extremely complex), take a look at the code below ...

DECLARE @date1a datetime,@date2a datetime,@date1b datetime,@date2b datetime -- + other vars needed
DECLARE @status smallint -- return statuus for SP

CREATE TABLE #temp(date1a datetime,
                   date2a datetime,
                   date1b datetime,
                   date2b datetime) -- + other columns if needed

  SELECT a.date1,a.date2,b.date1,b.date2 -- + other columns needed
    FROM table1 a, table2 b
   WHERE = FOR READ ONLY -- whatever join condition is required
OPEN csr

  FETCH NEXT FROM csr INTO @date1a,@date2a,@date1b,@date2b -- + other variables corresp. to columns
  EXEC @status = timespan @date1a,@date2a,@date1b,@date2b

  IF @status = 1 BEGIN
    INSERT INTO #temp(date1a,date2a,date1b,date2b) -- + other column names
    VALUES (@date1a,@date2a,@date1b,@date2b)       -- + other data




-- temporary table #temp will be dropped automatically when sp finishes


Expert Comment

ID: 1089409

There is no way to call a stored proc from within a where clause.  What mitek is suggesting is pretty much what I suggested except that he is using the return code from the stored proc instead of variables that you pass to the stored proc.  Either should work.  It's up to you if want to experience the joy of using a cursor.  :)

I think the best solution for you would be to include your select in the stored proc that figures timspan as I suggested in my previous post.

That way instead of saying:
select * from table where exec sp_stored_proc

you would make your select the last line of your stored proc and then just call:
exec sp_stored_proc

If you're having trouble with this at all, feel free to post further questions (of course) or you can e-mail me direct if you choose.

Let me know if this works for you.  :)


Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question