CODEWITHSUNDEEP
sql
dnord
dnord

Reputation: 1742

converting function to stored procedure

I'm working in an app that has user functions throughout WHERE clauses in the data layer, and I'm sure it's causing problems with performance.

As an example, let's say there's a report pulling entries and comments, and there's a function 

where (dbo.CountComments(entries.id, '5/10/2011') = 0)

...showing us, let's say, the entries with no comments today.

I'd like to convert this to a stored proc, but it seems like it's nearly impossible to get the same behavior from a stored proc.

If you had to rewrite this as a SP, how would you do it?

Upvotes: 0

Views: 1902

Answers (3)

Shannon Lowder
Shannon Lowder

Reputation: 482

If you rewrite this to a stored procedure, you can't use it in a WHERE clause. Only functions will give you that ability.

However, if you take the code from the function, and put it into a stored procedure, then pass the date in as a parameter, you will be able to get results like you're getting now, except you're doing so with a stored procedure.

Upvotes: 0

Anne Porosoff
Anne Porosoff

Reputation: 1941

You might try using basic SQL. Perhaps something like the following:

SELECT   *
FROM     comments c
WHERE    NOT EXISTS(SELECT commentID FROM comments c2 WHERE dateCreated >= getDate() AND c.commentID = c2.commentID)

This would be more efficient than using a function on several levels. But I am making some basic assumptions about what you're trying to accomplish so it's completely possible I'm way off the mark.

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

A couple of thoughts.

First, using COUNT=0 to find out of there is none of something is inefficient. You're better off using

NOT EXISTS (SELECT...)

That way SQL can bail as soon as it finds a row to return false instead of having to visit all of them to return a non-zero count.

Second, where are you using this function in your queries? If you want ot use its output as query criteria as in your example you're not going to be able to do that with stored procedures.

What I find myself doing is writing functions or views for commonly-used queries and then wrap some of them in stored procedures when I want to return the rows they return. if I have to join the results against other tables or views, it's best to leave them as functions.

Upvotes: 2

Related Questions