Select * from special problems in user-defined functions (Views) (the solution is provided later)

Document directory

• Solution 3: recompile

Directory

• Raise Questions
• Analyze problems
• Solve the problem

 

 

 

1. Ask questions

In a user-defined Table value function, if select * from is returned, if the table structure is modified, for example, the return value of the field Table value function added to the table is different from the actual value.

Modify the table structure

Data in the table before modification

UDF

Search results before modifying the table structure

 

Modified table structure

Query results after modifying the table structure

2. analyze the problem

For example, you can see that the ttt column of the User-Defined Function query result is incorrect.

Returns the result of the createdate column, but the createdate column does not.

I assume that the select * from statement is automatically parsed into a specific column and saved when the UDF is compiled, but the query statement is saved directly.

Therefore, when the table structure table is matched by the array of the column, the first column matches the first column and the nth column matches the nth column,

The ttt column matches the added column createdate. The original ttt is not displayed.

3. Solve the problem:

1. I think the best way to avoid this is not to use Select *

2. recompile the custom function

3. If possible, regenerate the column of the custom function (for details, see the following correction )--

 

-- Revised content:

We can see that after modifying the table structure, the column of the UDF is not modified, which verifies my guess.

Solution 3: recompile

I wrote a re-compiled SQL statement myself.

The modified SQL statement:

Because of the modification, CreateDate is now changed to NuLL.

But the result is correct.

 

You are welcome to make suggestions and discussions.