SQL Server: CREATE FUNCTION with declare variables inside

Question

I would like to create a function in SQL Server.

In this function, I need to define some variables and then use it in the SELECT.

SQL looks like below:

CREATE FUNCTION [dbo].[MyFussnction]  
(
    @path [nvarchar](10)
)
RETURNS TABLE

BEGIN
    DECLARE @xx varchar(50);
    SET @xx = 'Windows%';
RETURN 
    SELECT * FROM MyTable WHERE DataPath LIKE @path AND XX LIKE @xx;
END

But, it is not able to be created and the error says:

Msg 102, Level 15, State 31, Procedure MyFussnction, Line 12 [Batch Start Line 0]
Incorrect syntax near 'BEGIN'.

Answer 1

You need to define columns of table to return, then you can use declare, something like below

CREATE FUNCTION [dbo].[MyFussnction]  (  
@path [nvarchar](10)
)
RETURNS @Mytable TABLE 
(
    ID int PRIMARY KEY NOT NULL
    -- define other columns
)
AS
BEGIN
    DECLARE @xx varchar(50);
    SET @xx = 'Windows%';

    Insert into @Mytable
    SELECT Id FROM MyTable WHERE DataPath LIKE @path AND XX LIKE @xx;
  RETURN; 
END