Microsoft SQL Server forum - sp_ExecuteSQL in User Defined Function

Author

sp_ExecuteSQL in User Defined Function

Highlander416

2005-07-20, 8:23 pm

This is driving me crazy.

I need to create a UDF that would return a TRUE/FALSE (bit) value based
on a comparison it does.

CREATE FUNCTION dbo. SelectedByApplicatio
n
(
@ApplicationID int,
@TableToCheck nvarc
har(50),
@ColumnToCompare nva
rchar(50),
@ValueInTable int
)
RETURNS BIT AS
BEGIN

DECLARE @SQL NVARCHAR(1000)
DECLARE @Param NVARCHAR(500)
DECLARE @Result int

SET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + '] '
+
'WHERE [' + @ColumnToCompare + '] = @ValueInTable AND
ApplicationID = @ApplicationID'
SET @Param = N'@result int out, @ValueInTable int, @ApplicationID
int'

EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,
@ApplicationID

if @result > 0
return 1
return 0
END

All I need the function to do is fill in a column based on whether
there is a relation between a list of data and the item. I'm trying to
use it in the following query:

SELECT *, EXEC dbo. SelectedByApplicatio
n(4, 'IPM_Application_Dat
aType',
'DataTypeID', DataTypeID)
FROM IPM_DataType DT

The idea is to make this call and then be able to populate a list of
checkboxes based on the information it returns. It should return
something similiar to:

Column1 Column2 UDFColumn
1 SomeValue 0
2 OtherValue 1
3 DifferentValue 0
4 LastValue 1

After reading some of the posts and discovering you can't execute
dynamic SQL in a UDF I decided to split the function into a function
and stored procedure:

CREATE FUNCTION dbo. SelectedByApplicatio
n
(
@ApplicationID int,
@TableToCheck nvarc
har(50),
@ColumnToCompare nva
rchar(50),
@ValueInTable int
)
RETURNS BIT AS
BEGIN

Declare @Result INT

EXEC DynamicCompare @ApplicationID, @TableToCheck, @ColumnToCompare,
@ValueInTable, @Result

if(@Result > 0)
return 1
return 0
END

CREATE PROCEDURE dbo.DynamicCompare
(
@ApplicationID int,
@TableToCheck nvarc
har(50),
@ColumnToCompare nva
rchar(50),
@ValueInTable int,
@Result int out
)
AS
DECLARE @SQL NVARCHAR(1000)
DECLARE @Param NVARCHAR(500)

SET @SQL = N'SELECT @result = COUNT(*) FROM [' + @TableToCheck + ']
' +
'WHERE [' + @ColumnToCompare + '] = @ValueInTable AND
ApplicationID = @ApplicationID'
SET @Param = N'@result int out, @ValueInTable int, @ApplicationID
int'

EXECUTE sp_executesql @SQL, @Param, @result out, @ValueInTable,
@ApplicationID

I get the same error message about only being able to execute functions
and extended stored procedures in a user defined function.

Does anyone have any ideas as to how I can dynamically execute this
query? The reason I say dynamic is I need this same comparison for
about 25 different tables. Thanks!

Erland Sommarskog

2005-07-20, 8:24 pm

Highlander416 (rbradford@gmail.com) writes:
> Does anyone have any ideas as to how I can dynamically execute this
> query? The reason I say dynamic is I need this same comparison for
> about 25 different tables. Thanks!

You cannot invoke dynamic SQL from a user-defined function.

I don't really get a grip of what you are trying to do. Even less why.
And that would be kind of interesting to know.

Because, normally, if find yourself wanting to do such a thing, it's
a strong indication that there is a problem with the data model. Then
again, it could be OK, if this is for some special purpose, for instance
auditing or reconciliation, and part of the main application code.

If you can expand on the underlyingh business problem you have, it may
be easier to suggest a strategy.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Highlander416

2005-07-21, 9:23 am

Hey Erland,

I will describe the business problem I'm trying to solve, but first let
me mention there is another alternative to finding yourself trying to
do such a thing: a problem is being over-complicated. I ended up using
a Stored Procedure that incorporated both items in code. It's not
perfect (in fact - a little clunky), but it did solve the problem.

I am generating a form (intended for print) that will be populated with
information from a web app (should it exist). This form is meant for
developers to use to interview various businesses within an
organization to determine what systems (software) they are currently
using. The intent is to capture information about these systems and
then to use that for planning purposes, strategic alignment, etc.
Since I'm taking a web based form (several screens and what not) and
turning it into a form intended for print, I had to overcome certain
challenges: particularly those involving drop-down lists that must now
become groups of check-boxes. This is why I needed the bit field - to
pre-populate the necessary check boxes.

So here's the solutions I ended up with:

CREATE PROCEDURE dbo. SelectedByApplicatio
n
(
@FromTable NVarChar(50) = 'IPM_DataType',
@ValueColumnToCompar
e NVarChar(50) = 'DataTypeID',
@TableToCheck NVarChar(50) = 'IPM_Application_Dat
aType',
@ColumnToCompare NVarChar(50) = 'DataTypeID',
@ApplicationID INT = 4
)
AS

DECLARE @SQL NVarChar(1000)
DECLARE @Param NVarChar(500)

SET @SQL = N'SELECT *, (SELECT COUNT(*) FROM [' + @TableToCheck + ']
WHERE [' + @ColumnToCompare + '] = T.[' + @ValueColumnToCompar
e + ']
' +
'AND ApplicationID = @ApplicationID) AS Selected ' +
'FROM [' + @FromTable + '] AS T'
SET @Param = N'@ApplicationID INT'
EXEC sp_ExecuteSQL @SQL, @Param, @ApplicationID

As I said, it is a bit clunky - but it does perform the task I need.
Let me know if you have any suggestions for improvement.

Sponsored Links