We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

User Defined Functions in SQL statements

vvg
vvg asked
on
Medium Priority
462 Views
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.

Comment
Watch Question

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

or

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.

Example

create procedure sp1 as
declare
  @var1 int,
  @var2 int
begin

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

exec sp2 @var1, @var2

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

end


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

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

end


I hope this is helpful!  :)

Don
vvg

Author

Commented:
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.

Regards
vvg.


Commented:
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 . . .

Zimmy
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
wg,

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.

don@mayhewnet.com

Let me know if this works for you.  :)

Don
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.