TVF not returning data & I can't make sense of it

Transact-SQL
1

I am not able to figure out why a tvf does not work within a USP.

My TVF looks something like this & it is defined

SELECT
	[id]
	, [field1]
	, [field2]
FROM [mydb].[dbo].[tvf_dataset_search]()

And the definition of the TVF is something like this.

SELECT
	[id]
	, [field1]
	, [field2]
FROM [mydb].[dbo].[sometable]

The USP to update a dataset looks like this

EXEC [mydb].[dbo].[usp_dataset_update] @id, @field1, @field2

And is defined something like this:

UPDATE [mydb].[dbo].[sometable]
SET field1 = @field1
	, field2 = @field2 
WHERE id = @id

...

Here is the problem. When I add this to the USP, it works.

SELECT * FROM [mydb].[dbo].[sometable]
WHERE ID = SCOPE_IDENTITY()

But when I add this, it doesn't work the first time, but does work each additional time.

SELECT
	[id]
	, [field1]
	, [field2]
FROM [mydb].[dbo].[tvf_dataset_search]()
WHERE ID = SCOPE_IDENTITY()

At the moment I have two copies of the TVF query (which is much more complicated than the snippet above). So long as the raw SQL is in the USP, it will work. But the moment I nest the TVF into the USP it stops working.

I have checked the filters & the ID is returned. In fact, the same TVF when called outside of the USP always works.

I've tried adding option (recompile). I've also tried adding the SCOPE_IDENTITY to a temporary table to assure it exists before the query is called. The data is there, but the TVF does not return it.

Any thoughts?

EDIT - To clarify, when I say have to copies, I mean that after the update is made, I have to have all of the raw SQL for the select statement again (below) instead of calling the TVF. Also it's worth clarifying that this is a simplified example. There are multiple tables with many columns & the headers are renamed in specific ways.

UPDATE [mydb].[dbo].[sometable]
SET field1 = @field1
	, field2 = @field2 
WHERE id = @id
;
SELECT
	[id]
	, [field1]
	, [field2]
FROM [mydb].[dbo].[tvf_dataset_search]()
WHERE ID = SCOPE_IDENTITY()
2
  1. You need to provide a minimal reproducible example.
  2. What has SCOPE_IDENTITY() got to do with an update?. New identities will only be produced with an insert. Maybe you just need to use the OUTPUT clause.

ps Are you using RLS?

3

Does the proc owner have permission/authority to invoke the TVF?

4

This is what I meant: --- Define tableCREATE TABLE dbo.x_tmp ( [id] [int] IDENTITY(1,1) NOT NULL,f - Pastebin.com

In the example code, both [usp_set_x_tmp_functional] & [usp_set_x_tmp_nonfunctional] work. I'm not able to reproduce the mistake in sample code but I'm also not able to avoid the mistake in production code.

This post is to request help in figuring out what the issue might be.

You are right, this only occurs on inserts, not updates. And I do not use RLS.

5

It does not. Though you're right that it's one of the first things to check & I should have specified. The user has permission to read/write on all tables, procedures & functions

6

Any reason why you do not use the OUTPUT clause?

INSERT INTO dbo.x_tmp( field1, field2)
OUTPUT inserted.id, inserted.field1, inserted.field2
VALUES(@field1, @field2);
7

I tried by modifying the SQL this way. I get the same results as SCOPE_IDENTITY. It does not return anything the first time but it returns the correct row each additional time.

Possibly it is not an answerable question since I can't define the problem. For now I might have to stick with having a copy of the TVF & to be careful as I edit.

DECLARE @inserted_row table([id] INT);
... -- More SQL

... -- Insert statement
OUTPUT inserted.id 
INTO @inserted_row
... -- More SQL

SELECT *
FROM [mydb].[dbo].[tvf_dataset_search]()
WHERE [ID] = ( SELECT ID FROM @inserted_row )