1

I have a multi statement table valued function which I would like to change to an inline table valued function for optimization purposes ( I don't really know if that will be an optimization but I want to try that anyway ). My problem is that I have a scalar variable which I don't know how to put in my WITH statement.

Code example:

CREATE FUNCTION [dbo].[function]()
RETURNS 
  @return_table table (id INT,value NVARCHAR(500)) 
    AS
    BEGIN
      DECLARE @tmp_table        TABLE(id INT, value VARCHAR(500))
      DECLARE @variable     BIGINT
      INSERT INTO @tmp_table [...insert code...]
      SET @variable = (SELECT MAX(id) FROM @tmp_table)
    INSERT INTO @return_table SELECT id,value FROM @tmp_table WHERE id = @variable
RETURN

This code is an example, the actual function is more complex but the problem is exactly the same

I could easily change this to a single WITH statement like this:

CREATE FUNCTION  [dbo].[function]()
RETURNS TABLE
AS
RETURN 
(
    WITH tmp_table AS (
       SELECT [...Select Code...]
    )
    SELECT id,value FROM tmp_table 
    WHERE id = [variable]
);
GO

My problem lies into the [variable] which I don't know how to put into the query. Also, the variable is used more than once in my function so I'd rather not just replace it with the query.

I also tried this approach:

CREATE FUNCTION  [dbo].[function]()
RETURNS TABLE
AS
RETURN 
(
    WITH tmp_table AS (
       SELECT [...Select Code...]
    ), variable = (SELECT MAX(id) value FROM tmp_table)
    SELECT id,value FROM tmp_table 
    WHERE id = (SELECT TOP 1 value FROM variable)
);
GO

But is seems like it made the function way slower.

Thank you.

1 Answer 1

1

Just try

WITH tmp_table AS (
   SELECT [...Select Code...]
)
SELECT id,value FROM tmp_table WHERE id = (SELECT MAX(id) FROM tmp_table)

I would actually just change it to

SELECT TOP 1 *
FROm [whatever]
ORDER BY id DESC
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you but as I stated, this is not the actual query I use, this is just an example just to point out my problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.