CODEWITHSUNDEEP
t-sqluser-defined-functions
unitario
unitario

Reputation: 6535

Problem with executing statement returned from an UDF - no result set

I have made a User-Defined Function that returns a WHERE statement based on some values in another table.

WHERE dbo.tblGeneralLedgerEntry.accountID = 1210

Then I use a stored procedure to construct and execute the SELECT statement:

SELECT dbo.tblGeneralLedgerEntry.*, dbo.tblJournalAccountHead.description As header
FROM dbo.tblGeneralLedgerEntry
INNER JOIN dbo.tblJournalAccountHead ON dbo.tblGeneralLedgerEntry.documentReference = dbo.tblJournalAccountHead.documentReference
WHERE dbo.tblGeneralLedgerEntry.accountID = 1210

Everything runs successfully but I am getting empty results. When I just execute the statement without using the function I will get results, so I know that is not the problem.

Stored procedure (@reportID = 10000)

DECLARE @sql_select nvarchar(1000)

IF (@reportID = 10000) BEGIN 
    SET @sql_select = 
        'SELECT dbo.tblGeneralLedgerEntry.*, dbo.tblJournalAccountHead.description As header ' +
        'FROM dbo.tblGeneralLedgerEntry ' + 
        'INNER JOIN dbo.tblJournalAccountHead ON dbo.tblGeneralLedgerEntry.documentReference = dbo.tblJournalAccountHead.documentReference '
END

DECLARE @sql nvarchar(4000) = @sql_select + dbo.reportWhere(@reportID)

EXEC ( @sql )

Using MS-SQL with Access 2010 in an ADP.

What am I doing wrong?

Thanks!

EDIT

The reason it was not working is apperantly is because it is not supposed to:

Dynamic SQL in User-Defined Functions

This very simple: you cannot use dynamic SQL from used-defined functions written in T-SQL. This is because you are not permitted do anything in a UDF that could change the database state (as the UDF may be invoked as part of a query). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.

Source:The Curse and Blessings of Dynamic SQL

Upvotes: 0

Views: 330

Answers (2)

Aaron Kempf
Aaron Kempf

Reputation: 588

Stefan;

I think that you have that backwards. UDF are allowed in dynamic-SQL. Dynamic-SQL is not allowed in UDFs.

For example

DECLARE @sql_select nvarchar(1000)

IF (@reportID = 10000) BEGIN SELECT @sql_select = 'SELECT dbo.tblGeneralLedgerEntry.*, dbo.tblJournalAccountHead.description As header ' + 'FROM dbo.tblGeneralLedgerEntry ' + 'INNER JOIN dbo.tblJournalAccountHead ON dbo.tblGeneralLedgerEntry.documentReference = dbo.tblJournalAccountHead.documentReference ' + dbo.reportWhere(@reportID) END

EXEC ( @sql )

-Aaron MCITP: DBA

Upvotes: 1

kutsoff
kutsoff

Reputation: 345

I do not understand in your question, where the stored procedure, where the function, how they are supposed to be. I think there is enough of a function that returns a table value, for example:

CREATE FUNCTION dbo.fn_MyFunc(@AccountID int)
returns @Result Table(field1 f1type, ..., Header varchar(50))
as
BEGIN

Insert into @Result(field1 f1type, ..., Header varchar(50))
SELECT dbo.tblGeneralLedgerEntry.*, dbo.tblJournalAccountHead.description As header
FROM dbo.tblGeneralLedgerEntry
INNER JOIN dbo.tblJournalAccountHead ON dbo.tblGeneralLedgerEntry.documentReference = dbo.tblJournalAccountHead.documentReference
WHERE dbo.tblGeneralLedgerEntry.accountID = @AccountID

Return

END

Using as:

select * from dbo.fn_MyFunc(dbo.reportWhere(@reportID))

In this case the UDF dbo.ReportWhere must return an integer scalar value

Upvotes: 0

Related Questions