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.