Calling a function from within a query that needs to execute dynamic SQL

Question

Post reply

Calling a function from within a query that needs to execute dynamic SQL

Andre Beier

SSC Veteran

Points: 270
More actions
October 3, 2006 at 12:15 pm

#114720

Hi,
Here is a simplified version of a query:
-----------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT CategoryID, QCategoryName, SortOrder, Expanded, Criteria FROM dbo.vQAllQuestionsWithAnswers
-----------------------------------------------------------------------------------------------------------------------------
The next step is that I want to call a function and pass in the Criteria as a Parameter
-----------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT CategoryID, QCategoryName, SortOrder, Expanded, MyFunction(Criteria) FROM dbo.vQAllQuestionsWithAnswers
-----------------------------------------------------------------------------------------------------------------------------
So far so good.
This function should run a SQL statement that will return the RecordCount.
The Criteria is something like this: " STATE = 'AL' AND ZIP = 12345 "
Simplified version of my function:
-----------------------------------------------------------------------------------------------------------------------------
DECLARE @Count int
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT @Count = COUNT(*) FROM Table WHERE ' + @Criteria
EXECUTE sp_executesql @sql, N'@Count int OUT', @Count OUT
SELECT @Count
-----------------------------------------------------------------------------------------------------------------------------
Unfortunately UDF don't allow the execution of dynamic queries.
I have thought of the following solutions already but none of them work:
a) Use a stored proc instead of a UDF
Problem: a stored proc can not be called from within the query
b) Use cursors
Problem: Using a cursor returns a DataSet back to my .NET application that has 1 table for each row of data
c) Let the Web server do the work
Problem: Pperformance issues and complexity of implementation

Any suggestions?
Andre

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

First, I do not understand how this works if the UDF will not allow dynamic queries...?

SELECT DISTINCT CategoryID, QCategoryName, SortOrder, Expanded, MyFunction(Criteria) FROM dbo.vQAllQuestionsWithAnswers

-----------------------------------------------------------------------------------------

So far so good.

Unfortunately UDF don't allow the execution of dynamic queries.

-----------------------------------------------------------------------------------------

Second, why can you not call a stored proc from within a stored proc...?

I have thought of the following solutions already but none of them work:

a) Use a stored proc instead of a UDF

Problem: a stored proc can not be called from within the query

I wasn't born stupid - I had to study.

Andre Beier SSC Veteran Points: 270 More actions · Answer 2

First, maybe I thought have articulated this better. I was trying to give an explaination of my thought process and I got stuck at the function that has the dynamic SQL statement.

Second:

When I try to execute this within a stored procedure I get an error that it is not possible

SELECT fieldA, SP_MyStoredProc(Criteria), ... FROM Table

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I think I get you. Can you wrap the whole thing in sp_executesql?

I wasn't born stupid - I had to study.

Andre Beier SSC Veteran Points: 270 More actions · Answer 4

I don't see how this can help me, the criteria can be different for each row of data

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Holy cow! I did not realize that... I guess you would have to loop and insert the data into a temp table, (of course, scope then becomes an issue) and query that... This is a booger.

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Option d) is missing:

Use a subquery.

Option e):

use output parameter and assign @@rowcount to it

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Good thinking! [ Sheesh..., I must 'a left my bulldozer at home.... ]

I wasn't born stupid - I had to study.

Andre Beier SSC Veteran Points: 270 More actions · Answer 8

How can I use a subquery with the way the criteria field is storing the WHERE statement?

Again, an example for the criteria value is: " STATE = 'AL' AND ZIP = 12345 "

So the dynamic query would be " SELECT Count(*) FROM Table WHERE " + Criteria.

I don't see how that will work with a subquery nor using rowcount

I am going back to the idea of using a cursor and loop through each record.

This is more promising at the moment except that I have not figured out how to prevent that the fetched row will be returned as a table in the dataset, I only want to return the temp table.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

1) Don't ever pass @Criteria parameter.

Have you ever heard about SQL injections?

2) Use WHERE (State = @State OR @State IS NULL)

3) What is the connection between main query and subquery you wanted to put into function?

Why you possibly need to calculate rows in a table not related to the queried recordset?

_____________
Code for TallyGenerator